½Ð°Ý HLOOKUP ªº table_array ³o³¡¤À¨Ï¥Î¦WºÙ©w¸q¬O§_¥i¦æ¡I

a8552065

·Q¥Î HLOOKUP ªº table_array ³o³¡¤À¨Ï¥Î¦WºÙ©w¸q
¨Ã¥B¥Î¤@­ÓÀx¦s®æ¬ö¿ýÀɮתº¦ì¸m = 'D:\[´¸«Bªí.xlsx]¤Ñ®ðª¬ªp²Î­p'!$D$3:$AAR$6

Sheets("1.¤Á´«").Range("A16") = 'D:\[´¸«Bªí.xlsx]¤Ñ®ðª¬ªp²Î­p'!$D$3:$AAR$6
¦ý¬O¦b¦WºÙºÞ²z¾¹ªº³¡¤À½TÅã¥Ü ="D:\[´¸«Bªí.xlsx]¤Ñ®ðª¬ªp²Î­p'!$D$3:$AAR$6"¡A¾É­PHLOOKUPªº table_array ½d³ò¿ù»~¡C

¬ÛÃöVBA¦p¤U¡G

Sub test() '³z¹Lvba¡AÅýhlookup¥i¥H¥Î"¦WºÙ©w¸q"ªº¤è¦¡¡A§Ö³t°Ñ¦Ò´¸«Bªí¦ì¸m

Dim a As String

a = Sheets("1.¤Á´«").Range("A16") '.Value

ActiveWorkbook.Names.Add Name:="´¸«Bªí", RefersTo:=a

End Sub

¤w¸g´ú¸Õ¹L¦r²Å¦êªº¤è¦¡¤]µLªk¹F¦¨,¦³ªø½ú³B²z¹L³o¼Ëªº°ÝÃD¶Ü¡I¥ý·PÁ¤F

GBKEE

¦^´_ 1# a8552065

¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Wb(1 To 2) As Variant, Rng(1 To 2) As Range, MyWb As Workbook
   
4.     Set MyWb = ActiveWorkbook
   
5.     Set Rng(1) = MyWb.Sheets("1.¤Á´«").Range("A16")     ''D:\[´¸«Bªí.xlsx]¤Ñ®ðª¬ªp²Î­p'!$D$3:$AAR$6
   
6.     Wb(1) = Split(Rng(1), "]")(0)                                       ''D:\[´¸«Bªí.xlsx
   
7.     Wb(1) = Replace(Wb(1), "[", "")                                   ''D:\´¸«Bªí.xlsx
   
8.     Wb(2) = Split(Split(Rng(1), "]")(1), "!")                        'Wb(2)(0)='¤Ñ®ðª¬ªp²Î­p' , Wb(2)(1)=$D$3:$AAR$6
   
9.     With Workbooks.Open(Wb(1))
   
10.         Set Rng(2) = .Sheets(Wb(2)(0)).Range(Wb(2)(1))     'D:\[´¸«Bªí.xlsx]¤Ñ®ðª¬ªp²Î­p'!$D$3:$AAR$6
    
11.         MyWb.Names.Add Name:=" ´¸«Bªí", RefersTo:=Rng(2)
    
12.         .Close
    
13.    End With
    
14. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 1# a8552065
A16Àx¦s®æ«e­±ÃB¥~¦h¥[¤@­Ó³æ¤Þ¸¹¬Ý¬Ý¡C