使用 VBA 在动态范围内使用 VLOOKUP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18473197/
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
Use VLOOKUP in a dynamic range using VBA
提问by
I'm trying to set up VB so I can do a VLOOKUP on a dynamic range of values. It'll always start at the same place, but it may end further down based on whatever value is in H4.
我正在尝试设置 VB，以便我可以对动态范围的值执行 VLOOKUP。它总是从同一个地方开始，但根据 H4 中的任何值，它可能会进一步下降。
EDIT: Here's the code and it works.
编辑：这是代码，它可以工作。
Thank you Alex!
谢谢亚历克斯！
Public Sub State()
Dim refRng As Range, ref As Range, dataRng As Range
Dim i As Variant
Dim count As Integer
i = Sheet2.Range("H1").Value
i = i + 3 'offset of when to start
Set refRng = Sheet2.Range("D8:" & Cells(8, i).Address) '//horizontal range of look up values
Set dataRng = Sheet13.Range("A:C") '//data block you want to look up value in
For Each ref In refRng
ref.Offset(1, 0) = Application.WorksheetFunction.VLookup(refRng, dataRng, 2, True)
Next ref
End Sub
采纳答案by Alex P
This may help:
这可能有帮助：
Sub LookUp()
Dim refRng As Range, ref As Range, dataRng As Range
Set refRng = Worksheets(1).Range("D8:F8") //horizontal range of look up values
Set dataRng = Worksheets(2).Range("A1:B4") //data block you want to look up value in
For Each ref In refRng
ref.Offset(1, 0) = WorksheetFunction.VLookup(ref, dataRng, 2, 0)
Next ref
End Sub
Here you set up references to your lookup values and the data you want to query. Then just iterate over the values in the horizontal range and look up the value.
您可以在此处设置对查找值和要查询的数据的引用。然后只需遍历水平范围内的值并查找值。
