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]

Glad to help. Now go to bed. LOL

 

[RAJESH1960]

Glad to help. Now go to bed. LOL

Need to grab some sleep - now happily and peacefully?

 

[johnnyL]

For closure, the following code would be the 'New' formula with what you were trying to combine with the previous formula variable used.

    With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IF(MATCH(1,((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$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]

    'FormulaReplacementString1 = "MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(" & DSCol & "2-$" & DSCol & "$2:$" & _
      '      DSCol & "$20000)<=1)*($B$2:$B$20000=""PORTAL""),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(" & _
     '       DSCol & "2-$" & DSCol & "$2:$" & DSCol & "$20000)<=1)*($B$2:$B$20000=""TALLY""),1,0)))"     ' Additional string to insert into 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
    
    With wsDestination.Range(DestinationRemarksColumn & "2")
         .FormulaArray = "=IF(MATCH(1,((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$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
        .Replace "xxxxxx", FormulaReplacementString1, xlPart                                            '   Insert additional string into formula
    
    End With
    
    '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
'

I tested with both the edited codes. I am facing another problem now. I have commented the old codes for reference. I first tested it the lines not commented in the above code, with the line .Replace "xxxxxx", FormulaReplacementString1, xlPart ' Insert additional string into formula
and tested again after deleting this line. In both the cases I am getting 2 unwanted rows in Edited portal sheet which are copied to the mismatched sheet too.

 

[RAJESH1960]

 

[RAJESH1960]

These last 2 lines were not appearing with the old formula. So, the error has to be due to the lines with the formula which were edited.

 

[RAJESH1960]

Deleted the whole code and tested again replacing only your final formula. It is good now. ?