Replace old formula with new one in the code.

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
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.
Rich (BB 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
Rich (BB code):
=  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"))))
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
JohnnyL. This is the last one which you shared in the link. I replaced the lines but I am getting an error. Maybe I didn't replace the right lines. Later I deleted the replaced lines and brought it to its original working form with the old formula.
Continue Match Portal.xlsm
I did that already. Do You want the one I replaced the formula and tried..???
 
Upvote 0
I want to see a workbook that shows the formulas and the matches from those formulas.
 
Upvote 0
Here ya go:

VBA Code:
Sub SortColumnAndApplyFormulas(HeaderTitle As String)
'
    Dim ColumnFirstZeroValueRow     As Long
    Dim LastRow                     As Long
    Dim DSCol                       As String       ' DestinationSortColumn
    Dim IfReplacementString1        As String
    Dim IfReplacementString2        As String
    Dim SecondIfReplacementString1  As String
    Dim MultiplyReplacementString1  As String
    Dim MultiplyReplacementString2  As String
    Dim MultiplyReplacementString3  As String
    Dim MultiplyReplacementString4  As String
    Dim MultiplyReplacementString5  As String
'
    With wsDestination
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        DSCol = Split(Cells(1, .Range("1:1").Find(HeaderTitle).Column).Address, "$")(1)                 ' Find Column letter of the HeaderTitle we are looking for
'
'       RANGE SORTER ... Least important column to most important column
        .Range("A2:O" & DestinationLastRow).Sort Key1:=.Range(DSCol & "2"), _
                Order1:=xlDescending, Header:=xlNo                                                      ' Sort HeaderTitle Column highest to lowest
'
        ColumnFirstZeroValueRow = .Range(DSCol & "1:" & DSCol & .Range("A" & _
                Rows.Count).End(xlUp).Row).Find(what:=0, LookAt:=xlWhole, SearchDirection:=xlNext).Row  ' Locate first row in column with a zero value
'
' Replacement strings to insert into formula
        SecondIfReplacementString1 = "IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$" & LastRow & ")<=1)*99991*99993)>SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & _
                DSCol & "$" & LastRow & ")<=1)*99991*99992), B9999)"
        IfReplacementString1 = "IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "2)<=1)*99994*99995)<= SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$" & LastRow & ")<=1)*99991*99993), ""Matched"", ""Not Found"")"
        IfReplacementString2 = "IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "2)<=1)*99994*99995)<= SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$" & LastRow & ")<=1)*99991*99992),""Matched"", ""Not Found"")"
        MultiplyReplacementString1 = "(C2=$C$2:$C$" & LastRow & ")"
        MultiplyReplacementString2 = "(""Portal""=$B$2:$B$" & LastRow & ")"
        MultiplyReplacementString3 = "(""Tally""=$B$2:$B$" & LastRow & ")"
        MultiplyReplacementString4 = "(C2=$C$2:$C2)"
        MultiplyReplacementString5 = "(B2=$B$2:$B2)"
'
        With .Range(DestinationRemarksColumn & "2")
            .FormulaArray = "=IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                    "$" & LastRow & ")<=1)*99991*99992)=SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                    "$" & LastRow & ")<=1)*99991*99993), ""Matched"", IF(SUM((ABS(" & DSCol & "2-$" & DSCol & _
                    "$2:$" & DSCol & "$" & LastRow & ")<=1)*99991*99992)>SUM((ABS(" & DSCol & "2-$" & DSCol & _
                    "$2:$" & DSCol & "$" & LastRow & ")<=1)*99991*99993), A9999, C9999))"               '   Formula to insert into 'Remarks' column
'
' Variables to replace, string used to replace the variable
            .Replace "C9999", SecondIfReplacementString1, xlPart
            .Replace "A9999", IfReplacementString1, xlPart
            .Replace "B9999", IfReplacementString2, xlPart
            .Replace "99991", MultiplyReplacementString1, xlPart
            .Replace "99992", MultiplyReplacementString2, xlPart
            .Replace "99993", MultiplyReplacementString3, xlPart
            .Replace "99994", MultiplyReplacementString4, xlPart
            .Replace "99995", MultiplyReplacementString5, xlPart
        End With
'
        .Range(DestinationRemarksColumn & "2").AutoFill .Range(DestinationRemarksColumn & _
                "2:" & DestinationRemarksColumn & ColumnFirstZeroValueRow - 1)                          ' Drag the formula down till zero value is found
'
        .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & _
                ColumnFirstZeroValueRow - 1).Copy                                                       ' Copy formula range into memory
        .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & _
                ColumnFirstZeroValueRow - 1).PasteSpecial xlPasteValues                                 ' Paste just the vales back to range
        Application.CutCopyMode = False                                                                 ' Clear clipboard & 'marching ants' around copied range
    End With
End Sub
 
Upvote 0
Solution
It is working now. I tried deleting 2 rows in Journal and tried that too. Both are working fine. Thanks buddy. I was afraid whether it will take such a big formula or not. But all is well. It is solved now.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top