"Vlookup" and "Match" in VBA

am0251

§Ú¥­±`³£·|¥Î«Ü¦h"Vlookup" ¸ò "Match",¦pªG§Ú·Q¥ÎVBAªº¤è¦¡¼g¥X¨Ó,¸Ó«ç»ò°µ©O?

am0251

ÁÂÁ¦^ÂÐ,¥i±¤§ÚÅv­­¤£°÷,¤£¯à¤U¸ü,¥i¥H²³æªº¤¶²Ð¤@¤U¶Ü?

am0251

ÁÂÁÂ,§Ú¸Õ¤F...
x = Range("D" & z).Application.Formula = " [=MATCH(Sheets1!""ItemName"",Sheets2!A:A,0)]"
´N¬O¤£¦æ,¥i¥H§i¶D§Ú,§Ú¿ù¦b­þ¸Ì¶Ü?
ÁÂÁÂ~~!

am0251

«Ü©_©Ç,§Ú¸Õ¹L:
ItemNam = Range("A" & z).Value
x = Application.WorksheetFunction.Match(""ItemNam"",, Sheets("OUT").Range("A:A"))
¬O¤£¦æªº,¥u¦³¤£¥ÎÅܼÆ"ItemNam"¦Óª½±µ§â"Range("A" & z).Value"¥´¶i¥h¤~¯à¥Î Book1.rar (12.98 KB)

¤U¸ü¦¸¼Æ: 31

2011-6-22 17:12

am0251

ÁÂÁ«ü±Ð~~!!·Pı¥\¤O´£ª@¤F¤£¤p!!

am0251

¦pªG§Ú·Q§âÅܼÆ"y"ªº­Èµ¥©ó,§ÚªºUserFrom ¤ºªº Calendar ªº­È,¥i¬O§Ú¼g¤F"y = Calendar1.Value"¥X¨Óªºµ²ªG¬O"1/¤»¤ë/2011 ",§ÚªºÄݩʤw¸g§ï"­^¤å(²µu)"ªº¤F,­n¦b­þ¸Ì¥i¥H§ï¦¨"1-Jun-11"©O?
¦]¬°§Ú­n§â"x" "y"Åܦ¨Cells(x,y)ªºÚµ¼Ð,"x"´N¬OItemName,"y"´N¬O¤é´Á,¦pB2="1-Jun-11"; C2="2-Jun-11"............¦³¥i¯à°µªº¨ì¶Ü?

am0251

¤£¦n·N«ä......§Ú¤S¥¢±Ñ¤F...... Book1.rar (22.21 KB)

¤U¸ü¦¸¼Æ: 7

2011-6-23 16:52

am0251

ÁÂÁ«üÂI,¤w§ï¦n¤F,¥i¬O.......ÁÙ¬O¤£¦æ......... Book1.rar (23.12 KB)

¤U¸ü¦¸¼Æ: 2

2011-6-24 15:36

am0251

¤£¦n·N«ä,¤pªº¨S¬ÝÀ´....³o¬O§Úªºµ{§Ç

1. Private Sub Start_Click()
   
2. 
   
3. Dim x As Integer, y As Integer, z As Integer, S1 As Integer, S2 As Integer, myRowCount As Integer
   
4. Dim ShipDate As Date
   
5. Dim ItemName As String
   
6. 
   
7. z = 2
   
8. 
   
9. myRowCount = Range("A1").CurrentRegion.Rows.Count
   
10. 
    
11. ItemName = Worksheets("Sheet1").Range("A" & z).Value
    
12. 
    
13. ShipDate = Format(Calendar1.Value, "d-mmm-yy")
    
14. 
    
15. For i = 2 To myRowCount
    
16. 
    
17. x = Application.Match(ItemName, Sheet2.["A1", "A" & myRowCount], 0)
    
18. y = Application.Match(ShipDate, Sheet2.[B1:AF1], 0)
    
19. 
    
20. 
    
21. S1 = Workbooks("Book1").Worksheets("Sheet1").Cells(z, 2).Value
    
22. S2 = Workbooks("Book1").Worksheets("Sheet2").Cells(x, y).Value
    
23. 
    
24. S2 = S2 + S1
    
25. 
    
26. z = z + 1
    
27. 
    
28. Next i
    
29. 
    
30. Unload Me
    
31. 
    
32. MsgBox "Done"
    
33. 
    
34. End Sub

½Æ»s¥N½X

am0251

ÁÂÁÂHsieh¤j¤jªº¥X¤â¬Û§U......¥i¬OÁÙ¬O¤£¦æ.........,¥i¥H¦A§i¶D§Ú,§Ú¤S¿ù¦b­þ¸Ì©O?¸U¤G¤À·PÁÂ!

1. Private Sub Start_Click()
   
2. 
   
3. Dim x As Integer, y As Integer, z As Integer, S1 As Integer, S2 As Integer, myRowCount As Integer
   
4. Dim ShipDate As Double
   
5. Dim ItemName As String
   
6. 
   
7. z = 2
   
8. 
   
9. myRowCount = Range("A1").CurrentRegion.Rows.Count
   
10. 
    
11. ShipDate = Format(Calendar1.Value, "d-mmm-yy")
    
12. 
    
13. For i = 2 To myRowCount
    
14. 
    
15. ItemName = Worksheets("Sheet1").Range("A" & z).Value
    
16. 
    
17. x = Application.Match(ItemName, Sheet2.Range("A1:A" & myRowCount), 0)
    
18. y = Application.Match(ShipDate, Sheet2.[B1:AF1], 0)
    
19. 
    
20. S1 = Workbooks("Book1").Worksheets("Sheet2").Cells(x, y).Value
    
21. S2 = Workbooks("Book1").Worksheets("Sheet1").Cells(z, 2).Value
    
22. 
    
23. S1 = S1 + S2
    
24. 
    
25. z = z + 1
    
26. 
    
27. Next i
    
28. 
    
29. Unload Me
    
30. 
    
31. MsgBox "Done"
    
32. 
    
33. End Sub

½Æ»s¥N½X