CODEWITHSUNDEEP
arraysexcelarray-formulasvba
RugsKid
RugsKid

Reputation: 344

Array Formula VIa VBA Applied to Blank Cells GIving Error

I have macro that is attempting to check a column to see if there are any blank cells. In any blank cells I need to place an array formula.

The array formula functions fine on sheet and it is:

=INDEX($CJ$1:$CJ$100,MATCH(1,INDEX(($CL$1:$CL$100=CL2)*($CJ$1:$CJ$100<>""),0),0))

In the macro I cannot use fixed ranges so CJ100 and CL100 Need to be CL "LastRow" and CJ "LastRow" as the data ranges are dynamic.

Thus far I have this code:

Dim ws as worksheet
 Dim LastRow as long
Dim i As Long

set ws = worksheets("Sheet1")

With ws


LastRow = ws.Range("B" & rows.Count).End(xlUp).Row


For i = 1 To LastRow
    If ws.Range("CJ" & i).Value = "" Then


Range("CJ" & i).FormulaArray = "=index(CJ1:CJ ""& LastRow"",MATCH(1,INDEX((CL1:CL ""& LastRow"" = CL ""&i"")*(CJ1:CJ ""& LastRow <>""""),0),0))"

End If
Next i
End With

I am getting an error on this line: Range("CJ" & i).FormulaArray = "=index(CJ1:CJ ""& LastRow"",MATCH(1,INDEX((CL1:CL ""& LastRow"" = CL ""&i"")*(CJ1:CJ ""& LastRow <>""""),0),0))"

The error is the dreaded "Unable to set the FormulaArray property of the Range class".

After doing some research, it appeared that because I am applying to formula to one cell at a time, perhaps I do not need to use .FormulaArray I change the problem line to use .formula = "-=.... but this resulted in a "Application defined or object defined error" message.

Thinking the issue may be with my use of range I tried the following variation:

Dim LastRow as long
Dim ws as worksheet

set ws = worksheets("Sheet1")

LastRow = ws.Range("B" & rows.Count).End(xlUp).Row
Set rng = ws.Range("CJ2:CJ" & LastRow)

For Each cell In rng
  'test value in column CJ
   If cell.Value = "" Then
        'inserts formula
cell.FormulaArray = "=index(CJ1:CJ ""& LastRow"",MATCH(1,INDEX((CL1:CL ""& LastRow"" = CL2)*(CJ1:CJ ""& LastRow <>""""),0),0))"
'cell.arrayformula
    End If
 Next
End With

This results in the same Formula Array error as my code at the top of the post.

I don't believe the formula is over 250 characters unless I am counting characters incorrectly? Perhaps there is a syntax error somewhere?

Upvotes: 1

Views: 269

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Your implementation of Array formula is incorrect and you have also not qualified the range reference correctly as preceding dot is missing.

Range("CJ" & i).FormulaArray = "=index(CJ1:CJ ""& LastRow"",MATCH(1,INDEX((CL1:CL ""& LastRow"" = CL ""&i"")*(CJ1:CJ ""& LastRow <>""""),0),0))"

Here's updated code which should work for you:

Sub ApplyArrayFormula()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long

    Set ws = Worksheets("Sheet1")

    With ws


    LastRow = .Range("B" & Rows.Count).End(xlUp).Row


        For i = 1 To LastRow
            If .Range("CJ" & i).Value = "" Then
                .Range("CJ" & i).FormulaArray = "=INDEX($CJ$1:$CJ$" & LastRow & ",MATCH(1,INDEX(($CL$1:$CL$" & LastRow & "=CL" & i & ")*($CJ$1:$CJ$" & LastRow & "<>""""),0),0))"
            End If
        Next i
    End With
End Sub

Upvotes: 2

Related Questions