FormulaArray in Excel Macro

[goliathh]

Hi all,

I have been referring to this forum quite a fair bit recently to learn about Excel Macro from fellow experts in here. And for that, I want to thank you all first.

I have also referred some threads related FormulaArray too... which comes to the main reason of this thread.

I know that FormulaArray has its characters limitation and hence, I have tried playing around with Replace function to make my formula 'appear' shorter.

I have tried both options (1 & 2) below but somehow, they do not work for some reason.

And hence... the emerge of this thread asking for fellow experts in here to help me verify / troubleshoot my (messy) codes.

Below are the codes for...

Option 1

[FONT=Helvetica Neue][FONT=Courier New][SIZE=2]Sub CompleteHCData()

Dim LastRow4 As Long
Dim x As Long
Dim VAPP As Worksheet

    Sheets("Summary").Activate
    
    Set VAPP = ActiveWorkbook.Sheets("Plan Output")
    LastRow4 = VAPP.Cells(Rows.Count, 1).End(xlUp).Row
             
    For x = 2 To Rows.Count
               
        If Cells(x, 2).Value > 0 Then
        
            Cells(x, 16).FormulaArray = "=IF(ISNA(INDEX('Plan Output'!R2C1:R" & LastRow4 & "C10,MATCH(1," & "R" & x & "C" & "2='Plan Output'!R2C1:R" & LastRow4 & "C1)*(R1C16='Plan Output'!R2C4:R" & LastRow4 & "C4),FALSE),6)),0," & _
                                            "F_F_F"
            Cells(x, 16).Replace "F_F_F", "INDEX('Plan Output'!R2C1:R" & LastRow4 & "C10,MATCH(1," & "R" & x & "C" & "2='Plan Output'!R2C1:R" & LastRow4 & "C1)*(R1C16='Plan Output'!R2C4:R" & LastRow4 & "C4),FALSE),6)))", lookat:=xlPart
                      
       End If
              
       Next x

End Sub[/SIZE][/FONT][/FONT]

Option 2

[FONT=courier new][SIZE=2]Sub CompleteHCData()

Dim LastRow4 As Long
Dim x As Long
Dim VAPP As Worksheet
Dim formulaMLA As String
Dim formulaMLA2 As String

   Set VAPP = ActiveWorkbook.Sheets("Plan Output")
   LastRow4 = VAPP.Cells(Rows.Count, 1).End(xlUp).Row

   For x = 2 To Rows.Count

          With Cells(x, 16)

               formulaMLA = "INDEX('Plan Output'!R2C1:R" & LastRow4 & "C10,MATCH(1," & "R" & x & "C" & "2='Plan Output'!R2C1:R" & LastRow4 & "C1)*(R1C16='Plan Output'!R2C4:R" & LastRow4 & "C4),FALSE),6)"
               formulaMLA2 = "INDEX('Plan Output'!R2C1:R" & LastRow4 & "C10,MATCH(1," & "R" & x & "C" & "2='Plan Output'!R2C1:R" & LastRow4 & "C1)*(R1C16='Plan Output'!R2C4:R" & LastRow4 & "C4),FALSE),6))))"

               .FormulaArray = "=IF(ISNA(""INDX1""),0,""INDX2"")"
               .Replace What:="INDX1", Replacement:=formulaMLA, lookat:=xlPart
               .Replace What:="INDX2", Replacement:=formulaMLA2, lookat:=xlPart

           End With

        End if

        Next x

End Sub[/SIZE]
[/FONT]

Ultimately, the formula supposed to be as below for every row in column 16...

[SIZE=2][FONT=courier new]{=IF(ISNA(INDEX('Plan Output'!R2C1:R62C10,MATCH(1,R2C2='Plan Output'!R2C1:R62C1)*(R1C16='Plan Output'!R2C4:R62C4),FALSE),6))),0,(INDEX('Plan Output'!R2C1:R62C10,MATCH(1,R2C2='Plan Output'!R2C1:R62C1)*(R1C16='Plan Output'!R2C4:R62C4),FALSE),6))))}[/FONT][/SIZE]

Any help / suggestions would be much appreciated.

 

[Norie]

Which sheet are you putting the array formula(s) on?

 

[goliathh]

Which sheet are you putting the array formula(s) on?

I'm putting the formula on Summary sheet.

I've missed out in the Option 2

 

[Norie]

I think there are errors in the formulas stored in formulaMLA and formulaMLA2 and it should just be "=IF(ISNA(INDX1),0,INDX2)"

 

[goliathh]

I think there are errors in the formulas stored in formulaMLA and formulaMLA2 and it should just be "=IF(ISNA(INDX1),0,INDX2)"

Did a check on the formula and compare them with the original ones and they looked fine... unless I have missed something out.

I have changed the formula to "=IF(ISNA(INDX1),0,INDX2)" but when I run macro, the cell will appear with this formula:

{=IF(ISNA(INDX),0,INDX2)}