Forum Discussion

AnthonyC1730's avatar
AnthonyC1730
Copper Contributor
Sep 29, 2024

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's avatar
    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's avatar
Steve_SumProductCom
Iron Contributor

AnthonyC1730 In that line, the property FormulaR1C1 is expecting to get references in R1C1 format, but your references are in A1 format (from the lines below):

 

Set MDART_G2 = wb2.Sheets("2. Référenciel Article").Range("G:G")

Set MDART_CODE = wb2.Sheets("2. Référenciel Article").Range("A:A")

 

 

Does it work properly if you replace "FormulaR1C1" with "Formula" or "Formula2"?

AnthonyC1730's avatar
AnthonyC1730
Copper Contributor
Sep 30, 2024

Steve_SumProductCom 
I also tried with the piece of code (R1C1 format):

 

Set MDART_G2 = wb2.Sheets("2. Référenciel Article").Range("C7")
Set MDART_CODE = wb2.Sheets("2. Référenciel Article").Range("C1")

 

but I still have the same error when I execute the VBA at this line:

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

 

Thank you

 

Resources