Forum Discussion

Copper Contributor

Sep 29, 2024

Solved

Define variable in my VBA to clean my code (error)

Hello, I would like to define variable in the top of my VBA to reference file and range (column) to make then Index/match function. Defining variable at the top of my code will be more convenient...

excel

Macros and VBA

AnthonyC1730
Sep 30, 2024
Steve_SumProductCom
Thank you very much, I did a small adjustment to your code because it was taking the col 1 and 7 of the extract.xlsx instead of the source file (see print screen below).

But with the code below, it is working perfectly! Thank you. Very helpful.

.Range("B2:B" & NBrow1).FormulaR1C1 = "=INDEX(" & MDART_G2.Address(External:=True, ReferenceStyle:=xlR1C1) & ",MATCH(RC[-1]," & MDART_CODE.Address(External:=True, ReferenceStyle:=xlR1C1) & ",0))"

Steve_SumProductCom

MVP

Sep 30, 2024

In that line of code, you are dealing with text (the range address), so maybe you have forgotten to specify that for your ranges. You may need to add ".Address(, , xlR1C1)" as shown below. Use the xlR1C1 so that it returns the R1C1 notation.

MDART_G2.Address(, , xlR1C1)
and
MDART_CODE.Address(, , xlR1C1)

.Range("B2:B" & NBrow1).FormulaR1C1 = "=INDEX(" & MDART_G2.Address(, , xlR1C1) & ",MATCH(RC[-1]," & MDART_CODE.Address(, , xlR1C1) & ",0))"

AnthonyC1730

Copper Contributor

Sep 30, 2024

Steve_SumProductCom
Thank you very much, I did a small adjustment to your code because it was taking the col 1 and 7 of the extract.xlsx instead of the source file (see print screen below).

But with the code below, it is working perfectly! Thank you. Very helpful.

.Range("B2:B" & NBrow1).FormulaR1C1 = "=INDEX(" & MDART_G2.Address(External:=True, ReferenceStyle:=xlR1C1) & ",MATCH(RC[-1]," & MDART_CODE.Address(External:=True, ReferenceStyle:=xlR1C1) & ",0))"