具有多个“VLookup”s的VLookup

Question

我坚持用VBA编写VLookup的正确语法，在VBA中要返回多个列。

通常，您使用{2,3,4,5}，但是VBA不认为这是有效的。

Set Sheet1 = ActiveWorkbook.Sheets(1)
Set Sheet2 = ActiveWorkbook.Sheets(2)
Set Sheet3 = ActiveWorkbook.Sheets(2)

result = Application.WorksheetFunction.VLookup(Sheet2.Range("A2"), Sheet1.Range("AA9:AF20"), {2,3,5,6,7,8,9}, False)

我已经在网络上为这个解决方案做了无数次搜索，但是对于如何正确语法{2,3,5,6,7,8,9}并没有一个明确的答案。

Answer 1

WorksheetFunction无法执行数组公式。Evaluate可以。

所以

sFormula = "VLOOKUP(" & Sheet2.Range("A2").Address(external:=True) & "," & Sheet1.Range("AA9:AF20").Address(external:=True) & ",{2,3,5,6,7,8,9},FALSE)"

result = Evaluate(sFormula)

但是从AA到AF只有6列。因此，试图得到列7,8,9将导致一个错误。

所以也许

sFormula = "VLOOKUP(" & Sheet2.Range("A2").Address(external:=True) & "," & Sheet1.Range("AA9:AI20").Address(external:=True) & ",{2,3,5,6,7,8,9},FALSE)"

result = Evaluate(sFormula)

Answer 2

试着循环作为

sub Mlookup()
    dim i as integer
    dim res 

    for i = 1 to 5
        res = Application.WorksheetFunction.VLookup(Sheet2.Range("A2"), Sheet1.Range("AA9:AF20"), i, False)
        sheet1.range("A"&i").value = res
    next i

end sub