Errro - FormulaArray property of the Range class

[ibuhary]

Hi All,

I am facing "Unable to set the FormulaArray property of the Range class" error in the macro.

It happens at only in the second ArrayFunction line. This function will keep running in all the lines in the data, and the range keeps changing, so I am unable to define range variables and use inside functions or I don't know how to do it.

Any help to solve the issue will be much appreicated.

Thanks in Advance,

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim ws As Worksheet
    Dim LRow As Long
    Set ws = ThisWorkbook.Sheets("Master")

    With ws
       LRow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 3 To LRow
       If .Cells(i, 2).Value = 0 Then
           .Cells(i, 2).Select
            Selection.FormulaArray = _
                Replace("=IFERROR(INDEX(R[1]C[5]:R[#]C[5],MATCH(-RC[1],((R[1]C[-1]:R[#]C[-1]<>RC[-1])*(R[1]C:R[#]C=0)*(R[1]C[1]:R[#]C[1])),0),1),0)", "#", LRow - i)
            If .Cells(i, 2) <> 0 Then
                .Cells(i, 2).Value = .Cells(i, 2).Value
                .Cells(.Cells(i, 2).Value + 2, 2).Value = 1
                .Cells(i, 2).Value = 1
                Else
                .Cells(i, 2).Value = ""
                .Cells(i, 2).Select
                Selection.FormulaArray = _
                Replace("=INDEX(R[1]C[5]:R[#]C[5],MATCH(MIN(IF(ABS(R[1]C[1]:R[#]C[1]+RC[1])=0,"""",ABS(R[1]C[1]:R[#]C[1]+RC[1]))),((R[1]C[-1]:R[#]C[-1]<>RC[-1])*(R[1]C:R[#]C=0)*(R[1]C[1]:R[#]C[1]+RC[1]>=-2)*(R[1]C[1]:R[#]C[1]+RC[1]<=2)*(ABS(R[1]C[1]:R[#]C[1]+RC[1]))),0),1)", "#", "LRow")
                'Selection.FormulaArray = _
                "=INDEX(G4:G28,MATCH(MIN(IF(ABS(C4:C28+C3)=0,"",ABS(C4:C28+C3))),((A4:A28<>A3)*(B4:B28=0)*(C4:C28+C3>=-2)*(C4:C28+C3<=2)*(ABS(C4:C28+C3))),0),1)"
                If .Cells(i, 2) <> 0 Then
                .Cells(i, 2).Value = .Cells(i, 2).Value
                .Cells(.Cells(i, 2).Value + 2, 2).Value = 1
                .Cells(i, 2).Value = 1
                Else
                .Cells(i, 2).Value = ""
                End If
           End If
       Else
       End If
    Next i
    End With
End Sub

 

[ibuhary]

If I truncate the data to below 100 rows, the macro runs.

I have in second array function at last LRow-i which in numbers reach 100 when data reached 103 rows. Is the error relating to this, I am wondering !

VBA Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim ws As Worksheet
    Dim LRow As Long
    Set ws = ThisWorkbook.Sheets("Master")

    With ws
       LRow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 3 To LRow
       If .Cells(i, 2).Value = 0 Then
           .Cells(i, 2).Select
            Selection.FormulaArray = _
                Replace("=IFERROR(INDEX(R[1]C[5]:R[#]C[5],MATCH(-RC[1],((R[1]C[-1]:R[#]C[-1]<>RC[-1])*(R[1]C:R[#]C=0)*(R[1]C[1]:R[#]C[1])),0),1),0)", "#", LRow - i)
            If .Cells(i, 2) <> 0 Then
                .Cells(i, 2).Value = .Cells(i, 2).Value
                .Cells(.Cells(i, 2).Value + 2, 2).Value = 1
                .Cells(i, 2).Value = 1
                Else
                .Cells(i, 2).Value = ""
                .Cells(i, 2).Select
                Selection.FormulaArray = _
                Replace("=INDEX(R[1]C[5]:R[#]C[5],MATCH(MIN(IF(ABS(R[1]C[1]:R[#]C[1]+RC[1])=0,"""",ABS(R[1]C[1]:R[#]C[1]+RC[1]))),((R[1]C[-1]:R[#]C[-1]<>RC[-1])*(R[1]C:R[#]C=0)*(R[1]C[1]:R[#]C[1]+RC[1]>=-2)*(R[1]C[1]:R[#]C[1]+RC[1]<=2)*(ABS(R[1]C[1]:R[#]C[1]+RC[1]))),0),1)", "#", LRow-i)
                'Selection.FormulaArray = _
                "=INDEX(G4:G28,MATCH(MIN(IF(ABS(C4:C28+C3)=0,"",ABS(C4:C28+C3))),((A4:A28<>A3)*(B4:B28=0)*(C4:C28+C3>=-2)*(C4:C28+C3<=2)*(ABS(C4:C28+C3))),0),1)"
                If .Cells(i, 2) <> 0 Then
                .Cells(i, 2).Value = .Cells(i, 2).Value
                .Cells(.Cells(i, 2).Value + 2, 2).Value = 1
                .Cells(i, 2).Value = 1
                Else
                .Cells(i, 2).Value = ""
                End If
           End If
       Else
       End If
    Next i
    End With
End Sub

 

[Fluff]

Crossposted Unable to set the FormulaArray property of the Range class

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ibuhary]

I thought these two are different forums

 

[Fluff]

They are different forums, that's what cross posting is. Please read this Excelguru Help Site - A message to forum cross posters to understand better.

 

[ibuhary]

So what is the action I can take now ?

 

[Fluff]

You do not need to take any further action on this occasion as I have added the cross post links for you.
However if you cross post in future, you will need to supply the links yourself.