Excel - Changing Index-Match Formula to a VBA Action

2
votes

I used an Index Match formula in Excel VBA to find part number and description for tools out of database. It was an Index-Match formula with to condintions. first look up a match in certain array for tool type of a machine and than match it to a configoration from certain array.

My problem is that I want to change to lookup way because with the formula I write specific range and I want it to be flexable so that if I add a new row in the database I would not need to change the code. the the databse is a table name "Illuminators".

That was the formula with to conditions I used before and as you can see there is a cell refernce and if i would add a new tool to the databse table under the array, it would not look for it there.

 Selection.FormulaArray = "=INDEX(DB!R2C1:R21C7,MATCH(1,(DB_Illumiators!R2C1:R21C1=RC[1])*(DB_Illumiators!R2C2:R21C2=RC[2]),0),4)"

I use the formula in the main sheet in a loop and I use it from the databse work sheet in the second picture:

enter image description here

enter image description here

I will be grateful to those who help

Rafael

1
vbaexcelexcel-formulaarray-formulas
Change R2C1:R21C7 to C1:C7 or if you don't want to lookup an entire column (may be slow) change to something very large that you know data will not go beyond (e.g. - R2C1:R5000C7 – Scott Holtzman

1 Answers

5
votes

Change R21C7, R21C1 and R21C2 to INDEX(DB!C7, MATCH("zzz", DB!C1)), INDEX(DB!C1, MATCH("zzz", DB!C1)) and INDEX(DB!C2, MATCH("zzz", DB!C1)). These will dynamically adjust the terminating cell depending on the last text val;ue in DB!A:A.

Selection.FormulaArray = _
  "=INDEX(DB!R2C1:INDEX(DB!C7, MATCH(""zzz"", DB!C1)), MATCH(1, (DB_Illumiators!R2C1:INDEX(DB!C1, MATCH(""zzz"", DB!C1))=RC[1])*(DB_Illumiators!R2C2:INDEX(DB!C2, MATCH(""zzz"", DB!C1))=RC[2]), 0), 4)"