Replace old formula with new one in the code.

[RAJESH1960]

Hello guys,
I need to replace the old formula in the code with this new formula. I need your expert help to guide me and tell me which lines to comment and which line I should enter the new formula. The old formula is in the below wrap code.

        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 .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
'

The formula to be replaced is

=  IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  =  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched",
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched", "Not Found"),
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200) )   >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),"Matched", "Not Found"))))

 

[johnnyL]

Sweet!

 

[RAJESH1960]

Did you try it?

Yes. I had forgotten to move these lines you told me yesterday. I corrected them and EUREKA.

Call CheckSubTotal
'
    Application.ScreenUpdating = True                                                               ' Turn ScreenUpdating back on
        If Sheets("Mismatches").Range("B2") = "" Then
                    MsgBox "No MisMatches Found"
                    
                End If

 

[johnnyL]

Glad to help.

 

[johnnyL]

I was afraid whether it will take such a big formula or not. But all is well. It is solved now.

I chopped it up into many pieces to allow variable names to be used. That way it doesn't look so long in the vba code.

 

[RAJESH1960]

I will never understand that part if I have to re create something alike that again.

 

[johnnyL]

That is definitely not beginner VBA stuff. Excel is very picky on how you split up a formula like that.

 

[RAJESH1960]

I concur.

 

[johnnyL]

The formula was about 725 characters long, vba only allows array formulas up to 256 characters, so it had to be chopped up into smaller sections to get around that vba limitation.

 

[RAJESH1960]

This code needs a major operation - New query posted.

 

[RAJESH1960]

For Each ws In Worksheets                                                                       ' Loop through all worksheets in the workbook
        Select Case ws.Name
            Case Is = SourceSheet, DestinationSheet, "Conditions", "2B", "Matched", _
                    "Mismatches"                                                                    '       List of sheets to exclude
'               Skip these sheets
            Case Else                                                                               '       All other sheets ...
                Call GetDataFromDataSheet(ws.Name)                                                  '           Pass sheet name to the sub routine
        End Select

You think I should add Edited Portal and Sub Total in the list, If I run the code without deleting the old data. It will just overwrite the data right..