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

[µo°Ý] VBAÅý¥Î¤á¿ï¾Üvlookupªº¤å¥ó¦W,¸ê®Æ½d³òÄæ¼Æ

[µo°Ý] VBAÅý¥Î¤á¿ï¾Üvlookupªº¤å¥ó¦W,¸ê®Æ½d³òÄæ¼Æ

²{®É§Ú¦bWorkbook2.xlsx , Column J2:N2 ¤º, vlookup Datebase.xlsx ªº¼Æ¾Ú.

¦bªÅ®æN2¨Ï¥ÎVLOOKUP($A:$A,[Database.xlsx]Sheet1!$A:$F,2,0) ¨Ó¦^¶Ç­È.
¦ÓªÅ®æM3µ¥©óN2¦^¶Ç¸ê®Æ½d³òÄæ¼Æ+1  => VLOOKUP($A:$A,[Database.xlsx]Sheet1!$A:$F,3,0) ¨Ó¦^¶Ç­È.
¦ÓªÅ®æL3µ¥©óN2¦^¶Ç¸ê®Æ½d³òÄæ¼Æ+2  => VLOOKUP($A:$A,[Database.xlsx]Sheet1!$A:$F,4,0) ¨Ó¦^¶Ç­È.

Datebase.xlsx , <=¨C¤ë¦WºÙ·|¦³©Ò¤£¦P , ©Ò¥H§Ú·Q³]¸mVBA¸ß°Ý©M¿ï¾ÜFILE¦ì¸m.
Sheet1!$A:$F,2,0<= vlookup¸ê®Æ½d³òÄæ¼Æ¤£¤@©w¬O²Ä2Äæ, ©Ò¥H§Ú·Q³]¸mVBA¸ß°Ý »Ý­n¦^¶Ç²Ä´XÄæ¼Æªº¸ê®Æ  .
Sheet1!$A:$F<=¨C¤ë½d³ò·|©T©w ,¤@©w¦³columnA¶}©l

¥i¥H¸Ñµª¤@¤U¶Ü? ·P¿E.:(

vlookup2.zip (24.86 KB)

¦^´_ 1# boomf2

¥\¤O¨S«Ü¦n,¸Õ¸Õ¬Ý!!
Sub ex()
Application.ScreenUpdating = False
Source1 = Application.GetOpenFilename
x = InputBox("¬d¸ß¦ì¸m")
Workbooks.Open Source1
Windows("Workbook2.xlsm").Activate
r = [a65535].End(3).Row
[N2].Resize(r - 1) = "=VLOOKUP(A2,[" & Dir(Source1) & "]Sheet1!A:F," & x & ",FALSE)"
[M2].Resize(r - 1) = "=VLOOKUP(A2,[" & Dir(Source1) & "]Sheet1!A:F," & x + 1 & ",FALSE)"
[L2].Resize(r - 1) = "=VLOOKUP(A2,[" & Dir(Source1) & "]Sheet1!A:F," & x + 2 & ",FALSE)"
Workbooks(Dir(Source1)).Close False
Application.ScreenUpdating = True
End Sub

§Ú¬O¥ÎM2 & L2,¦pªG­n§ï¬°M3 & L3,½Ð±NM2 & L2§ï¦¨M3 & L3§Y¥i
¦ÓªÅ®æM3µ¥©óN2¦^¶Ç¸ê®Æ½d³òÄæ¼Æ+1&nbsp;&nbsp;=> VLOOKUP($A:$A,[Database.xlsx]Sheet1!$A:$F,3,0) ¨Ó¦^¶Ç­È.
¦ÓªÅ®æL3µ¥©óN2¦^¶Ç¸ê®Æ½d³òÄæ¼Æ+2&nbsp;&nbsp;=> VLOOKUP($A:$A,[Database.xlsx]Sheet1!$A:$F,4,0) ¨Ó¦^¶Ç­È.

TOP

ÁÂÁ§A
·Q°Ý¤@¤U,¦³¤èªk¥i¥H¤£«ü©wworkbook2.xlsm¶Ü? ¦]¬°§ÚÀɮצW·|¸g±`Åܧó.
¥t¥~, .End(3).Row ¬O¤°»ò·N«ä©O?
  1. Windows("Workbook2.xlsm").Activate
  2. r = [a65535].End(3).Row
½Æ»s¥N½X

TOP

¦^´_ 3# boomf2

½Õ¾ã¤@¤U
Sub ex()
Application.ScreenUpdating = False
Source1 = Application.GetOpenFilename
x = InputBox("¬d¸ß¦ì¸m")
r = [a65535].End(3).Row
[N2].Resize(r - 1) = "=VLOOKUP(A2,[" & Dir(Source1) & "]Sheet1!A:F," & x & ",FALSE)"
[M2].Resize(r - 1) = "=VLOOKUP(A2,[" & Dir(Source1) & "]Sheet1!A:F," & x + 1 & ",FALSE)"
[L2].Resize(r - 1) = "=VLOOKUP(A2,[" & Dir(Source1) & "]Sheet1!A:F," & x + 2 & ",FALSE)"
Application.ScreenUpdating = True
End Sub

End(3):end属©Ê,3ªí¥ÜXLUP

TOP

  1. FilePath = ThisWorkbook.FullName
  2. FileOnly = ThisWorkbook.Name
  3. PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))[/b]

  4. Application.ScreenUpdating = False
  5. Source1 = Application.GetOpenFilename
  6. X = InputBox
  7. Workbooks.Open Source1
  8. [b]Windows("FileOnly").Activate[/b]
  9. R = [a65535].End(3).Row
½Æ»s¥N½X
¥[¤F¥H¤W²ÊÅ骺CODE...¥i¬O¤£¦æ.¦³¤j¤j¥i¥HÀ°¦£¶Ü

TOP

¦^´_  boomf2

½Õ¾ã¤@¤U
Sub ex()
Application.ScreenUpdating = False
Source1 = Application.GetOp ...
jcchiang µoªí©ó 2019-12-2 17:01



    ¦¨¥\¤F=] !!!!!

TOP

·íWORKBOOK/DATA¦h¹L3¸U¦æ®É, ¶]«ÜºC...¦³¤èªk§ïCODING SPEED UP¶Ü?

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD