Forum Discussion
Define variable in my VBA to clean my code (error)
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))"
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"?
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
- Please share a screen shot or the exact text of the error message.
- Please find below a print screen of the error I get (I left other code commented that I tried too without success):
https://www.evernote.com/shard/s586/sh/8ef409e1-d72e-f940-88c0-2fc22790407b/Ct936I4kbE5zQVfj3dUpnOqrCdkqf5BlLasECd8-5p5pgyJxBFnwJ_aZcw
Thank you.- BTW - you can share screen shots directly in this forum by using the "Open full text editor" link to write your reply. Then you can just paste images directly into the reply.