Getting the Error Message: "Unable to set the FormulaArray property of the Range Class"

[charmd1]

Hi,

Thank you in advance for helping me. My goal is to instead of using the index/match function directly in the Excel cells, which looks up a value based on the concatenation of a date and value (unique). I would like to populate the cells in the backend us VBA with the index/match formula shown below, then copy and paste special those values in the same cells, so that the user only sees the actual values found by the index/match lookup and not see the actual excel index match formulas.

I started off by finding the starting row, ending row, and column number to create the range where I would like to perform the index match lookup. After that, I created the range, then used the FormulaArray property, but I got an error message. Not sure what I am doing wrong. Lastly, it'd be nice if the user can enter a date range and only the specified dates that match would get updated in the routine. Thanks again for your help!

Sub performanceUpdate()

Dim collatRange As Range
Dim sht As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim rt As Long, cntCol As Long
Dim strField As String, strFieldnm As String

Worksheets("Performance").Select

'Find the last column

Range("I2").Select
cntCol = Range(Selection, Selection.End(xlToRight)).Count


'Find the start and end row

rt = 1

'Starting row

Do While strFieldnm <> "DEAL PERFORMANCE"

If Cells(rt, 1).Value <> "DEAL PERFORMANCE" Then
rt = rt + 1
strFieldnm = Cells(rt, 1).Value
End If
Loop

Dim perfStartRow As Long, perfEndRow As Long

perfStartRow = rt + 1

'Ending Row

Do Until strFieldnm = "COLLATERAL"

If Cells(rt, 1).Value <> "COLLATERAL" Then
rt = rt + 1
strFieldnm = Cells(rt, 1).Value
End If
Loop

perfEndRow = rt - 1

Dim rngPerformance As Range


Set rngPerformance = Range(Cells(perfStartRow, 9), Cells(perfEndRow, cntCol))


rngPerformance.FormulaArray = "=IFERROR(INDEX(Performance_Detail!$A:$CD,MATCH(R2C&""-""&RC2,INDEX(Performance_Detail!$A:$CD,,MATCH(R1C2,Performance_Detail!R1,0)),0),MATCH(RC3,Performance_Detail!R1,0)),"")"

'rngPerformance.Select
rngPerformance.Copy
rngPerformance.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone

End sub

 

[Norie]

You are mixing up A1 and R1C1 notation in the formula.

Try replacing Performance_Detail!$A:$CD with Performance_Detail!C1C82 in the formula.

 

[charmd1]

Thank you. I tried your suggestion, but I am still getting the error message.

 

[Norie]

Oops, didn't notice the "" at the end of the formula, for that you need to double up.

rngPerformance.FormulaArray = "=IFERROR(INDEX(Performance_Detail!C1C82] ,MATCH(R2C&""-""&RC2,INDEX(Performance_Detail!C1C82 ,,MATCH(R1C2,Performance_Detail!R1,0)),0),MATCH(RC3,Performance_Detail!R1,0)),"""")"

 

[charmd1]

It worked! Thank you!