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 to update my code later on.

However, I always face an error message on this line:

Range(“B2:B” & NBrow1).FormulaR1C1 = “=INDEX(” & MDART_G2.Address(External:=True) & “,MATCH(RC1,” & MDART_CODE.Address(External:=True) & “,0))”

Thank you in advance for your support.

Anthony

CODE:

Sub Reformatage_report_G2_AFO_TEST()

Dim wb2 As Workbook
Dim cheminFichier2 As String
Dim wb2Name As String
cheminFichier2 = “https://sodiaal.sharepoint.com/sites/DAG-EquipeAchatsPackaging/Documents partages/General/PROC-OP/BDD ARTICLES G2/DB_article_G2.xlsx”
Set wb2 = Workbooks.Open(cheminFichier2)
Windows(“extract.xlsx”).Activate

‘////////////////////////////////////////////////////////////////

Dim MDART_G2 As Range
Dim MDART_CODE As Range
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”)

‘////////////////////////////////////////////////////////////////

Dim NBrow1 As Long
With ActiveSheet
NBrow1 = Range(“A” & .Rows.Count).End(xlUp).Row

Range(“B1”).Select
ActiveCell.FormulaR1C1 = “SCOPE G2”
Range(“B2:B” & NBrow1).FormulaR1C1 = “=INDEX(” & MDART_G2.Address(External:=True) & “,MATCH(RC1,” & MDART_CODE.Address(External:=True) & “,0))”

End With

End Sub

​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 to update my code later on.However, I always face an error message on this line:Range(“B2:B” & NBrow1).FormulaR1C1 = “=INDEX(” & MDART_G2.Address(External:=True) & “,MATCH(RC1,” & MDART_CODE.Address(External:=True) & “,0))” Thank you in advance for your support.Anthony   CODE: Sub Reformatage_report_G2_AFO_TEST()Dim wb2 As WorkbookDim cheminFichier2 As StringDim wb2Name As StringcheminFichier2 = “https://sodiaal.sharepoint.com/sites/DAG-EquipeAchatsPackaging/Documents partages/General/PROC-OP/BDD ARTICLES G2/DB_article_G2.xlsx”Set wb2 = Workbooks.Open(cheminFichier2)Windows(“extract.xlsx”).Activate’////////////////////////////////////////////////////////////////Dim MDART_G2 As RangeDim MDART_CODE As RangeSet 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”)’////////////////////////////////////////////////////////////////Dim NBrow1 As LongWith ActiveSheetNBrow1 = Range(“A” & .Rows.Count).End(xlUp).RowRange(“B1”).SelectActiveCell.FormulaR1C1 = “SCOPE G2″Range(“B2:B” & NBrow1).FormulaR1C1 = “=INDEX(” & MDART_G2.Address(External:=True) & “,MATCH(RC1,” & MDART_CODE.Address(External:=True) & “,0))”End WithEnd Sub   Read More

​