How to Make This Formula (Combination of IFERROR, Index and Match) Work in VBA?

[dager6]

Hi,

I am trying to code the following formula, a combination of IFERROR, INDEX, and Match, in VBA. Unfortunately, the code below does not work. Your help will be fully appreciated.

Dim i As Long
For i = 2 To X
Range("D" & i & ":BJ" & i & "").Select
Selection.FormulaR1C1 =_
"{=IFERROR(INDEX('Sheet2'!R" & i & "C3:R" & i & "C74,MATCH(TRUE,'Sheet2'!R" & i & "C3:R" & i & "C74='Sheet3'!R" & i & "C3,0)+'Sheet'!R1C3),0)}"
ActiveCell.Offset(1, 0).Select
Next i

Best,

Matt

 

[Jonmo1]

Welcome to the board.

You don't enter array formulas by actually typeing the {brackets}
Just like when you're entering an array formula by hand
You don't type the brackets, instead you press CTRL+SHIFT+ENTER

Try removing those brackets, and changing FormulaR1C1 to FormulaArray

 

[dager6]

Hi Jonmo,

Thanks a lot for your quick response.

I made the recommended changes. The same error message popped up: "Compile error: Expected: line number or Label or Statement or end of statement.", and "=IFERROR(INDEX('Sheet2'!R" was highlighted.

Thanks again for your help.

Best,

Matt

 

[Jonmo1]

When I copied your code and pasted it into my VBA
the whole thing gets highlighted Red.

I removed the {}
And also added a space between = and _
You had =_
That's invalid for a new line character, must be = _

This code works for me (at least no errors anyway)

Selection.FormulaR1C1 = _
 "=IFERROR(INDEX('Sheet2'!R" & i & "C3:R" & i & "C74,MATCH(TRUE,'Sheet2'!R" & i & "C3:R" & i & "C74='Sheet3'!R" & i & "C3,0)+'Sheet'!R1C3),0)"

I also noticed you might have a typo there for the last sheetname, highlighted red.

 

[dager6]

Hi Jonmo,

It works perfectly after I made the corrections that you recommended again. Thank you very much!

Have a wonderful day,

Matt

 

[Jonmo1]

You're welcome.