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

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

¦^´_ 4# john2006168

   

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

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

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


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

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD