vba excel：索引并与vba匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44816414/
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
excel: index and match with vba
提问by paulinhax
I have this formula on the first column in my sheet:
我的工作表的第一列中有这个公式：
=IFERROR(INDEX(Plan2!$A:$K;MATCH(Plan3!B2;Plan2!$B:$B;0);MATCH(Plan3!$A;Plan2!$A:$K;0));"")
And it fits perfectly for what I want: look up on Plan2 (my databse) for the information on column B of Plan3 by matching the result by matching the header of table.
它非常适合我想要的：通过匹配表头来匹配结果，在 Plan2（我的数据库）上查找有关 Plan3 的 B 列的信息。
What I want know is to translate this to a VBA that do the same thing. This is what I've tried so far:
我想知道的是将其转换为执行相同操作的 VBA。这是我迄今为止尝试过的：
Sub AlocSubs()
Dim i As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Plan2")
Set ws2 = Sheets("Plan3")
For i = 2 To 20
ws2.Cells(i, 1).Value = Application.WorksheetFunction.Index(ws1.Range("A1:K20"), .match(ws2.Range("B2"), ws1.Range("B1:B20"), 0), .match(ws2.Range("A1"), ws1.Range("A1:K1"), 0))
Next i
End Sub
When I try to run I get the message:
当我尝试运行时，我收到以下消息：
Compilation error: Reference is not valid.
编译错误：引用无效。
And I get this line highlighted:
我突出显示了这一行：
Sub AlocSubs()
This is the first time that I try to translate a formula do a code in VBA so I really don't know what is going wrong.
这是我第一次尝试在 VBA 中翻译公式做代码，所以我真的不知道出了什么问题。
Any suggestions will be appreciated.
任何建议将不胜感激。
采纳答案by Mrig
Try this:
尝试这个：
Sub AlocSubs()
Dim i As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim strFormula As String
Set ws1 = Sheets("Plan2")
Set ws2 = Sheets("Plan3")
strFormula = "=IFERROR(INDEX(Plan2!$A:$K;MATCH(Plan3!B2;Plan2!$B:$B;0);MATCH(Plan3!$A;Plan2!$A:$K;0));"""")"
With ws2
With .Range(.Cells(2, 1), .Cells(20, 1))
.Formula = strFormula
.Value = .Value
End With
End With
End Sub
Note:I've not tested your formula. Code shows how the formula result can be displayed using VBA.
注意：我没有测试你的公式。代码显示了如何使用 VBA 显示公式结果。
回答by Egan Wolf
You used .match, but you don't have Withstatement before. You also don't use iin your formula. I guess it should be like this:
您使用了.match，但您With之前没有声明。你也不i在你的公式中使用。我想应该是这样的：
With Application.WorksheetFunction
For i = 2 To 20
ws2.Cells(i, 1).Value = .Index(ws1.Range("A1:K20"), .Match(ws2.Range("B" & i), ws1.Range("B1:B20"), 0), .Match(ws2.Range("A1"), ws1.Range("A1:K1"), 0))
Next i
End With
