如何在VBA中将变量传递给双匹配函数 [英] How to pass variables to an double match function in VBA
问题描述
我在工作表中有一堆行和25列,需要使用VBA基于列B和C在第4列中查找值。我正在使用索引和多个条件匹配函数的组合。
I have a bunch of rows and 25 columns in a worksheet, and need to find the value in the 4th column based on columns B and C using VBA. I am using a combination of index and multiple condition match functions.
我试图通过 https://www.mrexcel.com/forum/showthread.php?650832-VBA-Multiple-Criteria-Index-匹配和将整数变量传递给vba数组公式无济于事。
I tried to follow along via https://www.mrexcel.com/forum/showthread.php?650832-VBA-Multiple-Criteria-Index-Match and pass an integer variable into vba array formula to no avail.
我制作了这个有效的宏:
I made this macro which works:
Sub VariablesInArrayFormula()
SA = "Apples"
C1 = "Oranges"
Range("D27").Select
Selection.FormulaArray = "=index(A2:G27,match(1,(B2:B27=b4)*(C2:C27= c6),0),4)"
Range("E27").Select
Selection.FormulaArray = "=index(A2:G27,match(1,(B2:B27=""Apples"")*(C2:C27= ""Oranges""),0),4)"
f = Evaluate("index(A2:G27,match(1,(B2:B27=""Apples"")*(C2:C27= ""Oranges""),0),4)")
Range("G27").Select
Selection.FormulaArray = "=index(A2:G27,match(1,(B2:B27="" & SA & "")*(C2:C27= "" C1 ""),0),4)"
End Sub
我想将值分配给变量以备将来使用。
I want to assign the value to a variable for future use.
当我将其分配给D27时,它的工作原理是因为我指的是单元格引用b4和c6。
When I assign it to D27, it works because the I refer to cell references b4 and c6.
将它分配给单元格E27也可以,但是我需要直接引用苹果和橘子,我希望在哪里传入一个变量
Assigning it to cell E27 also works, but then I need to refer directly to Apples and Oranges, where as I would prefer to pass in a variables
当我传入苹果和橘子时,将它分配给一个变量f
assigning it to a variable f works when I pass in the words Apples and Oranges
当我尝试传递对Apples和Oranges(分别为SA和C1)的引用时,我收到 #N / A
错误。
when I attempt to pass a reference to Apples and Oranges (SA and C1 respectively), I receive a #N/A
error.
任何人都可以建议我可以将变量传递给此函数。
Can anyone suggest a way that I can pass in the variables to this function.
NB我尝试使用worksheetfunction.index和worksheetfunction.match并继续接收错误。具体来说,我试过:
NB I tried using worksheetfunction.index and worksheetfunction.match and kept receiving errors as well. Specifically, I tried:
gr4 = WorksheetFunction.Index(Range("A2:G27"), WorksheetFunction.Match(1, ((Range("B2:B27") = SA) * (Range("C2:C27") = C1)), 0), 4)
返回运行时错误#13:类型不匹配。
which returned a run time error #13: type mismatch.
奇怪的是,使用2个匹配失败,就像我使用单个列检查匹配函数一样
Odd that using 2 matches failed, as when I use a single column to check with the match function worked
Sub vfhj()
SA = "Apples"
C1 = "Oranges"
gr3 = WorksheetFunction.Index(Range("C2:C27"), WorksheetFunction.Match(C1, Range("C2:C27"), 0))
End Sub
推荐答案
如果我理解正确,我认为你的语法略有偏差 - 你省略了一些&符并忽略了加倍的规则报价。也不需要选择。
If I understand correctly, I think your syntax was just off slightly - you omitted some ampersands and overlooked the rule about doubling up the quotes. Also no need to Select.
Range("G27").FormulaArray = "=index(A2:G27,match(1,(B2:B27=""" & SA & """)*(C2:C27=""" & C1 & """),0),4)"
这篇关于如何在VBA中将变量传递给双匹配函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!