ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA lookup data and check book problems

[µo°Ý] VBA lookup data and check book problems

John test VBA 01 (2).zip (38.94 KB)
¤p§Ì¦³¨Çvba µ{¦¡°ÝÃD,·Q½Ð±Ð¦U¦ì¦Ñ®vÀ°¦£.

¦pªþ¥ó:

1.§Ú·Q¿é¤J¤Fvendor Name ¥i¥H¦Û°Êlookup±qSHEETS""DTTA"  ªºaddress and Tel no.¥t¥~¦pªG¬Û¦Pªº¤½¥q,¦ý¬O¤£¦P¦a¦a§},.Åã¥Ü¶¶§Çªº²Ä1­Ó,¦ý¬O­n¦³«H®§´£¥Ü"½Ð¤p¤ß¤½¥q¦W"
2.2.¦pªG§Ú¦b"¿é¤J¸ê®Æ"¤ºclick "check  book B1",
¸ê®Æ·|Åã¥Ü"SHEETS"DATA".B2.VALUE  ;¦p¨Sclick,´N¤£Åã¥Ü

¤p§Ì¦³¨Çvba µ{¦¡°ÝÃD,·Q½Ð±Ð¦U¦ì¦Ñ®vÀ°¦£.
1.§Ú·Q¿é¤J¤Fvendor Name ¥i¥H¦Û°Êlookup±qSHEETS""DTTA"  ªºaddress and Tel no...
john2006168 µoªí©ó 2013-7-25 18:44

1. ±qªþÀɤ¤§ä¤£¨ì vendor Name, ¤]¨S¦³  SHEETS""DTTA"  ©Î¬O address »P Tel no...
2. §ä¤£¨ì "¿é¤J¸ê®Æ" ¤]¨S¦³ "check  book B1" «ö¯Ã
¬O¤£¬O¶Ç¿ùÀɮפF©O?

TOP

¦^´_ 2# luhpro

1. ±qªþÀɤ¤§ä¤£¨ì vendor Name, ¤]¨S¦³  SHEETS""DTTA"  ©Î¬O address »P Tel no...
2. §ä¤£¨ì "¿é¤J¸ê®Æ" ¤]¨S¦³ "check  book B1" «ö¯Ã
¬O¤£¬O¶Ç¿ùÀɮפF©O?

  1.sorry,¥´¿ù,¬OSHEETS""Data"  ,¦b¨º­¶¤º,
2.§A¥i¯àÁÙ¨S¦³enable marco

TOP

§Æ±æ¦³¦Ñ®v¥iÀ°¦£...

TOP

¦^´_ 4# john2006168

   

³o¨Ç¦WºÙªº½d³ò½Ð¾ã²z¦n
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE


    John test VBA 02.zip (31.98 KB)


½Ð¬Ýªþ¥ó

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-7-27 10:17 ½s¿è

¦^´_ 6# john2006168
2.¦pªG§Ú¦b"¿é¤J¸ê®Æ"¤ºclick "check  book B1",¸ê®Æ·|Åã¥Ü"SHEETS"DATA".B2.VALUE  ;¦p¨Sclick,´N¤£Åã¥Ü
check  book B1 ¦b¨º¸Ì??
  1. '³o¬ODataBasa¤u§@ªí¼Ò²Õªºµ{¦¡½X
  2. 'D9 ªºÅçÃÒ½d³ò­n«ü¦V vendor Name¤u§@ªíªº A3:A7
  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.     Dim I As Integer
  5.     On Error Resume Next
  6.     Application.EnableEvents = False
  7.     If Not Intersect(Target, Range("D9:F9")) Is Nothing Then '§ïÅܪºÀx¦s®æ ¦b D9:F9¤º
  8.           If Target(1).Value <> "" Then
  9.             I = Application.Match(Target, [Vendor_name], 0)  '´M§ä vendor Nameªº¦ì¸m
  10.             [D23] = [Vendor_name].Cells(I, 1)
  11.             [D24] = [Vendor_name].Cells(I, 2)
  12.             [D25] = [Vendor_name].Cells(I, 3)
  13.             I = Application.CountIf([Vendor_name], Target)   '­pºâ vendor Name ªº­Ó¼Æ
  14.             If I > 1 Then MsgBox "½Ð¤p¤ß¤½¥q¦W"
  15.         Else
  16.            [D23:D25] = ""
  17.         End If
  18.     End If
  19.     Application.EnableEvents = True
  20. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-7-27 17:55 ½s¿è

¦^´_ 7# GBKEE




John test VBA 02--rev.zip (29.18 KB)

    ¤£¦n·N«ä  ...resend the files to you.Thanks for your  great help.

check b1.png (94.8 KB)

check b1.png

TOP

¦^´_ 8# john2006168
³o¬O2003ª©


Tset.rar (39.61 KB)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2013/7/29 05:42): ¤W¶ÇÀÉ®×¥¢±Ñ

[attach]15618[/attach]¦^´_ 9# GBKEE

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD