How to pass dynamic variables to an equation in VBA

Asked Apr 23 '18 at 20:32

Active Apr 23 '18 at 21:27

Viewed 35 times

I have an equation in VBA

f = Evaluate("index(A2:G27,match(1,(B2:B27=""Apples"")*(C2:C27= ""Oranges""),0),4)")

But I would like to be able to refer to the ranges as dynamic, depending on other conditions. For example, another search will be range A46:G77, not A2:G27. Also, my matches will change as well to B46:B77 and C46:C77

Is there a way to pass in a variable so that I can alter the range of values used in the search.

I tried

f = Evaluate("index(A" & var1 & ":G" & var2 & ",match(1,(B2:B27=""Apples"")*(C2:C27= ""Oranges""),0),4)")

to no avail.

Any suggestions are greatly appreciated.

NBB: This question differs from How to pass variables to an double match function in VBA in that in this question I want the dimension of the referenced matrix to be altered, as my attempt to alter it failed from my background.

edited Apr 23 '18 at 21:27

asked Apr 23 '18 at 20:32

frank

3,036
7
33
65

how is this different than your other question? – Scott Craner Apr 23 '18 at 20:35
Did you only want to change part of your formula? What about B2:B27 etc ? – Tim Williams Apr 23 '18 at 20:45

How to pass dynamic variables to an equation in VBA

0 Answers0