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]

What part of my instructions are you uncertain about? You already deleted the proper sections that I mentioned. All that is left is to remove that old formula and replace it with your 'New' one.

 

[RAJESH1960]

I did that but the problem is that I am not getting any values in the Matched sheet whereas there are more than 50% Matched rows.

 

[johnnyL]

Ok, so post the code here that shows that you replaced the old formula code with the 'New' formula code. Then we can see what you are trying to use.

 

[RAJESH1960]

    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)*(F2=$F$2:$F$23200)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),xxxxxx)" & _
                ",""Matched"",NA()),NA())"                                                              '   Formula to insert into 'Remarks' column
    End With

 

[RAJESH1960]

*(F2=$F$2:$F$23200)

I just added this. That is all.

 

[johnnyL]

That looks nothing like your 'New' formula that you posted. You can't add to what is left of the old formula. REPLACE IT.

 

[RAJESH1960]

    With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                $23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),ROW($A$2:$A$23200),""""),xxxxxx)" & _
                ",""Matched"",NA()),NA())"                                                              '   Formula to insert into 'Remarks' column
    End With

I am getting a syntax error after I changed it this.

 

[RAJESH1960]

B2<>$B$2:$B$23200

In your code it is B2=$B$23200. I don't know how to edit this portion "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _

 

[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","",NA()),NA())"                                                              '   Formula to insert into 'Remarks' column
    End With

After replacing the whole formula, I am still getting a syntax error.

 

[johnnyL]

Now we are getting somewhere. That still doesn't match your 'New' formula though. It appears you added some some NA stuff on the end. Are you sure that is the formula that you want to use?