使用VBA在动态范围内使用VLOOKUP

问题描述

限时删除！！

我正在尝试设置VB，以便可以对动态范围的值进行VLOOKUP.它总是在同一位置开始，但根据H4中的任何值，它可能会进一步下降.

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.

这是代码，它可以正常工作.

Here's the code and it works.

谢谢亚历克斯！

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

推荐答案

这可能会有所帮助:

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.

这篇关于使用VBA在动态范围内使用VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..