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","")

 

[RAJESH1960]

I used control + shift + enter while entering the formula

 

[johnnyL]

Is that the 'Newer' formula that you want to use?

 

[RAJESH1960]

Is that the 'Newer' formula that you want to use?

yes

 

[johnnyL]

That formula is not going to work.

 

[RAJESH1960]

This is the only formula which is getting the maximum number of correct Matches. The old formula was getting more matches but it was getting the mismatched amounts too in the Matched sheet.

 

[RAJESH1960]

I tried 3 different formulas and this is the one which gets the correct and maximum matches.

 

[johnnyL]

You are not allowed to enter that formula with the 'NA' stuff at the end into excel with the CTRL+Shift+Enter, so what makes you think it will work in the VBA code?

 

[RAJESH1960]

Let me me explain how. When there are multiple same amounts in Tally and one amount in portal, the old formula picks up all the amounts of tally and one amount in portal and sends it to the Matched sheet. Whereas it has to select only one row each from tally and portal. And vice versa.

 

[RAJESH1960]

I am unaware of this. Now you tell me what is the solution and how do we move ahead. You are the boss.

You are not allowed to enter that formula with the 'NA' stuff

 

[johnnyL]

You have to provide a formula that is acceptable to excel. The 'New' formula from post#1 here is acceptable. You can enter it in the excel spreadsheet with Control+Shift+Enter and excel will wrap it with the 'curly braces'. That formula you are trying to use with the 'NA's at the end is not accepted by excel. Try it and see what happens when you use the Control+Shift+Enter.