VBA 对不同工作表中表格的引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20071155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 17:15:07 来源:igfitidea点击:
VBA reference to a table in different worksheet
提问by taiko
I'm trying to use vlookup in VBA.
我正在尝试在 VBA 中使用 vlookup。
- Filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
- Table1 = Sheet1.Range("A3:A7000") ' SiteID
- Table2 = [Filename]Sheet1.Range("A3:I13")
- Roww = Sheet1.Range("E2").Row
- Coll = Sheet1.Range("E2").Column
- For Each cl In Table1
- Sheet1.Cells(Roww, Coll) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
- Roww = Roww +1
- Next cl
- Filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="请选择一个文件")
- Table1 = Sheet1.Range("A3:A7000") ' SiteID
- Table2 = [文件名]Sheet1.Range("A3:I13")
- Roww = Sheet1.Range("E2").Row
- Coll = Sheet1.Range("E2").Column
- 对于表1中的每个cl
- Sheet1.Cells(Roww, Coll) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
- 行 = 行 +1
- 下一个
My question is how do I define the table located in different worksheet/file on line 3?
我的问题是如何定义位于第 3 行不同工作表/文件中的表格？
回答by stobin
This should work:
这应该有效：
Sub vLook()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Range1, myValue
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:\Code\Book4.xlsx")
Range1 = wb2.Sheets(1).Range("A1:C5")
myValue = Application.WorksheetFunction.VLookup("Test", Range1, 2, False)
End Sub
回答by Sam
You can set the workbook, worksheet and range to objects in VBA and refer to them by name..
您可以在 VBA 中将工作簿、工作表和范围设置为对象，并按名称引用它们。
example:
例子：
Sub test()
Dim filename As String
Dim fullRangeString As String
Dim returnValue As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
'get workbook path
filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
'set our workbook and open it
Set wb = Application.Workbooks.Open(filename)
'set our worksheet
Set ws = wb.Worksheets("Sheet1")
'set the range for vlookup
Set rng = ws.Range("A3:I13")
'Do what you need to here with the range (will get error (unable to get vlookup property of worksheet) if value doesn't exist
returnValue = Application.WorksheetFunction.VLookup("test4", rng, 2, False)
MsgBox returnValue
'If you need a fully declared range string for use in a vlookup formula, then
'you'll need something like this (this won't work if there is any spaces or special
'charactors in the sheet name
'fullRangeString = "[" & rng.Parent.Parent.Name & "]" _
& rng.Parent.Name & "!" & rng.Address
'Sheet1.Cells(10, 10).Formula = "=VLOOKUP(A1," & fullRangeString & ",8,False)"
'close workbook if you need to
wb.Close False
End Sub
