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

john2006168

John test VBA 01 (2).zip (38.94 KB)

¤U¸ü¦¸¼Æ: 14

2013-7-25 18:40

¤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¤£Åã¥Ü

luhpro

¤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?

¤U¸ü (142.92 KB)

2013-7-25 23:11

¤U¸ü (78.55 KB)

2013-7-25 23:11

¤U¸ü (90.04 KB)

2013-7-25 23:11

john2006168

¦^´_ 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

john2006168

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

GBKEE

¦^´_ 4# john2006168

   

¤U¸ü (14.48 KB)

2013-7-26 15:02

³o¨Ç¦WºÙªº½d³ò½Ð¾ã²z¦n

john2006168

¦^´_ 5# GBKEE


    John test VBA 02.zip (31.98 KB)

¤U¸ü¦¸¼Æ: 5

2013-7-26 19:04

½Ð¬Ýªþ¥ó

GBKEE

¦^´_ 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

john2006168

¦^´_ 7# GBKEE

¤U¸ü (94.8 KB)

2013-7-27 17:29

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

¤U¸ü¦¸¼Æ: 6

2013-7-27 17:24

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

GBKEE

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


Tset.rar (39.61 KB)

¤U¸ü¦¸¼Æ: 30

2013-7-27 20:05

john2006168

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