- ©«¤l
- 27
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 38
- ÂI¦W
- 0
- §@·~¨t²Î
- Win 7
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-9-19
- ³Ì«áµn¿ý
- 2014-2-18
|
¦³ÃöVlookupªº¥Îªk
¦U¦ì«e½ú¦n
¨Ï¥ÎVlookupªº®ÉÔ¸I¨ì¤@Ó°ÝÃD
¸ê®Æ§¨·í¤¤¦³¦U¤½¥qªº°]³ø¡A¶}±Ò¤@ÓÀÉ®×·QÂ^¨ú¦U¤½¥q°]³ø¤¤ªº³¡¤À¸ê°T
µ{¦¡½X¦pªþÀÉ¡A§ÚÁ`¦@¸Õ¤FA~D¦@4ºØ¤èªk¡A«e¨âºØ¥i¦æ¡A«á¨âºØ¤£¥i¦æ- Private Sub CommandButton1_Click()
- Dim StartTime, LastRow
- StartTime = Timer
- LastRow = Sheets(1).Range("A2").End(xlDown).Row '§ä¥X³Ì«á¤@µ§¸ê®Æ
- Application.ScreenUpdating = False
-
- For i = 2 To LastRow
-
- Dim A
- A = Range("A" & i).Value
- Workbooks.Open Filename:="C:\Users\KT\Documents\2014_backup\KT\§ë¸ê²z°]\Excel¤ÀªR¼Ò²Õ\" & A & "©u³ø.xlsx", UpdateLinks:=False, ReadOnly:=True
- '¤èªkA «ü©wÀx¦s®æ
- Range("E" & i).FormulaR1C1 = "=[" & A & "©u³ø.xlsx]ISQ!R4C2"
-
- '¤èªkB VLookup
-
- Range("E" & i).FormulaR1C1 = "=VLOOKUP(R[-" & i - 1 & "]C,[" & Range("A" & i).Value & "©u³ø.xlsx]ISQ!R1C1:R52C9, 2, FALSE)"
-
- '¤èªkC --> ¤£¥i¦æ, Åã¥Ü¿ù»~'1004' À³¥Îµ{¦¡©Îª«¥ó©w¸q¤Wªº¿ù»~
- Range("E2").FormulaR1C1 = _
- "=VLOOKUP(R[-1]C,[1101©u³ø.xlsx]ISQ!$A$1:$J$58,2,FALSE)"
-
- '¤èªkD --> ¤£¥i¦æ, ½sĶ¿ù»~ ¥²¶·¬O³¯z¦¡ªºµ²§À
- Range("E" & i).FormulaR1C1 = "=VLOOKUP(Range("E2").value,[" & Range("A" & i).Value & "©u³ø.xlsx]ISQ!R1C1:R52C9, 2, FALSE)"
-
- Windows(A & "©u³ø.xlsx").Close
-
- Next i
-
- Application.ScreenUpdating = True
- EndTime = Timer
- MsgBox "¥»¦¸¤U¸ü¦@ªá¶O¡G" & EndTime - StartTime & "¬í"
- End Sub
½Æ»s¥N½X ·Q½Ð«e½ú«ü¾É¬°¦ó¤èªkC»PD·|µo¥Í¿ù»~
ÁÂÁÂ |
|