VBA从不同的excel文件中查找不同的名称

Hi I do a vlookup in other excel spreadsheet in a different excel file. Now my problem is the name changes all the time.

你好，我在另一个excel文件中的其他excel电子表格中做vlookup。现在我的问题是名字一直在变。

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[TEST2.xlsx]Sheet1!C1:C8,8,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range("A" & rows.Count).End(xlUp).Row)
Range("B2:B" & Range("A" & rows.Count).End(xlUp).Row).Select

So basically it looks in TEST2.xlsx for the data it needs and than it puts in a list in my original excel file. But the TEST2 gonna change all the time, everytime I'm gonna use this macro

基本上它在TEST2中。xlsx用于它需要的数据，而不是放在我最初的excel文件中的列表中。但是TEST2会一直改变，每次我使用这个宏

How Can I do this?

我该怎么做呢?

1 个解决方案

#1

Not entirely sure what you require but if I'm right you could do something like this.
It's worth noting that it is not necessary to select cells first but rather you can reference them directly.

不完全确定你需要什么，但如果我是对的你可以做这样的事情。值得注意的是，不需要首先选择单元格，而是可以直接引用它们。

Sub lookupSub(dataFile)
Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],[" & dataFile & "]Sheet1!C1:C8,8,FALSE)"
Range("B2").AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Select
End Sub

You can then call this as follows:

你可以这样称呼它:

Call lookupSub("TEST2.xlsx")

Or if it works from within a loop you could do something like this:

或者如果它在循环中工作，你可以这样做:

For i = 1 To 10
    Call lookupSub("TEST" & i & ".xlsx")
Next i

#1