VBA macros : Compile error expecting end of statem

Question

I am trying to copy the data from a source workbook into target workbook using Index Match based on Unique Case ID.

thisworkbook.Worksheets("Check").range("B2:B" & lastRowDash).value = "=IFERROR(Index(src.Worksheets("Sheet1").range("B:B"), MATCH(ThisWorkbook.Worksheets("Check").range("A2").value, src.Worksheets("Sheet1").range("A:A")), 0), 0), ""Closed"")"

I get a Compile error as soon as I hit the enter button

Answer 1

Use .Address with external:=true to convert the VBA workbook/worksheet/range references to worksheet formula references.

thisworkbook.Worksheets("Check").range("B2:B" & lastRowDash).FORMULA = _
  "=IFERROR(INDEX(" & src.Worksheets("Sheet1").range("B:B").Address(external:=true) & ", MATCH(" & ThisWorkbook.Worksheets("Check").range("A2").Address(0, 0, external:=true) & ", " & src.Worksheets("Sheet1").range("A:A").Address(external:=true) & ", 0)), ""Closed"")"

It might be more legible if you assign the various addresses to variables.

dim addr1 as string, addr2 as string, addr3 as string

addr1 = src.Worksheets("Sheet1").range("B:B").Address(external:=true)
addr2 = ThisWorkbook.Worksheets("Check").range("A2").Address(0, 0, external:=true)
addr3 = src.Worksheets("Sheet1").range("A:A").Address(external:=true)

thisworkbook.Worksheets("Check").range("B2:B" & lastRowDash).FORMULA = _
  "=IFERROR(INDEX(" & addr1 & ", MATCH(" & addr2 & ", " & addr3 & ", 0)), ""Closed"")"