VBA Code to Enter Formula in the Cell as Array Formula

[hrayani]

Dear Friends,

The purpose of the below code is to enter formulas in the range of cells
It works fine for non Array Formulas for Columns R & S

But it does not work for columns AB, AC & AD because of array formuals I guess - or is it something else ?? because I can't the see the formula results

Can someone pls check

Private Sub ApplyFormulaToRangeOrders()

    Dim ws As Worksheet
    Dim formulaRange As Range
    
    ' Assign the sheet to a variable
    Set ws = Sheets("ORDERS")

   
    ' Apply formulas to PO & Article Concatenated
    Set formulaRange = ws.Range("R2:R1800")
    formulaRange.Formula = "=IF(ISBLANK(B2),"""",IF(B2<>B1,A2,IF(ISNUMBER(SEARCH(A2,R1)),R1,R1&"" - ""&A2)))"
    
    Set formulaRange = ws.Range("S2:S1800")
    formulaRange.Formula = "=IF(ISBLANK(B2),"""",IF(B2<>B1,F2,IF(ISNUMBER(SEARCH(F2,S1)),S1,S1&"" - ""&F2)))"
    
    ' Now converting formulas to values
    ws.Range("R2:S1800").Value = ws.Range("R2:S1800").Value
            
    ' Apply formulas to the DPI & FRI Columns
    Set formulaRange = ws.Range("AB2:AB1800")
    formulaRange.FormulaArray = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0)),"""")"
                
    Set formulaRange = ws.Range("AC2:AC1800")
    formulaRange.FormulaArray = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),"""")"
            
    Set formulaRange = ws.Range("AD2:AD1800")
    formulaRange.FormulaArray = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 3""=fri_dpi_labtest_category),0)),"""")"
End Sub

 

[Domenic]

You'll need to first enter the array formula in the first cell in the range, and then copy or fill down. Here's an example using fill down...

    ' Apply formulas to the DPI & FRI Columns
    Set formulaRange = ws.Range("AB2:AB1800")
    With formulaRange(1)
        .FormulaArray = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0)),"""")"
        .AutoFill Destination:=formulaRange, Type:=xlFillDefault
    End With

Hope this helps!

 

[hrayani]

or just as below - what do you suggest

 ws.Range("AA2").FormulaArray = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""DPI 20%""=fri_dpi_labtest_category),0)),"""")"
    ws.Range("AA2:AA1800").FillDown

 

[Domenic]

Actually, that's works as well . . .

Cheers!

 

[hrayani]

Actually, that's works as well . . .

Cheers!

Thanks!

 

[MARK858]

Just for the hell of it another possible option

    Set formulaRange = ws.Range("AB2:AB1800")
    With formulaRange
         .Formula = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0)),"""")"
         .FormulaArray = .FormulaR1C1
    End With