Unable to set the FormulaArray property of the Range Class

[murthyspd]

Hi
I am using Excel VBA to put an array formula using the FormulaArray property in the cell. But I get
"Unable to set the FormulaArray property of the Range Class" error

Sub ResponsibilityToAdd(Sht As String)


Dim LastRowRespToAdd As Integer
Dim LastRowinNewSheet As Integer
Dim i As Integer

Worksheets("ResponsibilityToBeAdded").Activate
LastRowRespToAdd = Range("A1").End(xlDown).Row



''=INDEX(ResponsibilityToBeAdded!A2:D701,MATCH(Sheet16!B2&Sheet16!C2&Sheet16!D2,TRIM(ResponsibilityToBeAdded!A2:A701)&TRIM(ResponsibilityToBeAdded!B2:B701)&TRIM(ResponsibilityToBeAdded!C2:C701),0),4)

Worksheets(Sht).Activate
LastRowinNewSheet = Range("A1").End(xlDown).Row



For i = 2 To LastRowinNewSheet

If Range("B" & i) = "Others" Then
Range("F" & i) = Range("C" & i)
Else

Range("F" & i).FormulaArray = "=INDEX(ResponsibilityToBeAdded!A2:D" & LastRowRespToAdd & ",MATCH(" & Sht & "!B" & i & Sht & "!C" & i & _
Sht & "!D" & i & ",TRIM(ResponsibilityToBeAdded!A2:A" & LastRowRespToAdd & ")&TRIM(ResponsibilityToBeAdded!B2:B" & LastRowRespToAdd & _
")&TRIM(ResponsibilityToBeAdded!C2:C" & LastRowRespToAdd & "),0),4)"
End If
Next i


End Sub

Can somebody assist what could be the cause

 

[pgc01]

Hi

The formula has probably more than 255 characters which is not allowed to assign directly.
You must do a 2 step assignment. First assign a simplified version of the formula and then replace part of it with the remaining.

I don't have time now to post a solution but you can seach this board for several posts with the solution.
If you can't find it post back and I'll post a link later.

 

[murthyspd]

Thanks pgc01. I could resolve the issue. The error was with the formula. The length was less than 255. Thanks for the help


Regards
Murthy

 

[pgc01]

I'm glad you have everything working ok!