Replace formula in this code

[RAJESH1960]

Hello guys, I need your help to replace the formula in the code with the new formula.

With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),xxxxxx)" & _
                ",""Matched"",NA()),NA())"                                                              '   Formula to insert into 'Remarks' column
        .Replace "xxxxxx", FormulaReplacementString1, xlPart                                            '   Insert additional string into formula
    End With

New formula

=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched","")

Old formula

=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(B2<>$B$2:$B$23200),0),"Matched","")

 

[johnnyL]

Of course you are. Compare the post #1 formula to your most recent attempt at the via code… hint you are missing something at the end.

 

[RAJESH1960]

I corrected it closing bracket right but still getting compile error.

 With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched","")                                                            '   Formula to insert into 'Remarks' column
    End With

 

[johnnyL]

Your formula is starting with a quote, I don't see where you ended it though.

 

[johnnyL]

Hint: .FormulaArray = "

 

[RAJESH1960]

With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray ="=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched","")"                                                            '   Formula to insert into 'Remarks' column
    End With

 

[RAJESH1960]

Compile error ...Zzzzzzzz.

 

[johnnyL]

Man we are so close now.

Now go back and read the last line of post #3.

 

[RAJESH1960]

Finally, Got it right. ROTF. It was so simple for you yet you made me stay awake so late. Ok It was worth it.
Thank you JohnnyL.

 

[johnnyL]

Finally!

You blame me when everything you needed was handed to you in post #3.

Well now you hopefully have learned how to convert the excel spreadsheet formula to vba code.

something further to sleep on ... .formula in vba code is equivalent with enter ..... .FormulaArray in vba code is equivalent with Control+Shift+Enter

 

[RAJESH1960]

Finally!

You blame me when everything you needed was handed to you in post #3.

Well now you hopefully have learned how to convert the excel spreadsheet formula to vba code.

something further to sleep on ... .formula in vba code is equivalent with enter ..... .FormulaArray in vba code is equivalent with Control+Shift+Enter

Yes. Thank you.