Need some additional changes in Match Portal code

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello guys
This code is already running perfect. I need your expertise to make some changes and add a few more lines of code to update this. I have tried to explain the steps in the conditions sheet as simple as possible.

Additonal Changes.xlsm
 
Last edited:
JohnnyL. All the issues are solved. All the sheets are created and arranged in the required order.
There is one final problem in the result. The mismatches sheet used to display Not Found, Reverse Charge Invoices and Exempted Invoices but now all the Not Found rows are showing Reverse Charge Invoices. If the Mismatched sheet is connected to the Match portal it has to display Not found in Portal or Not found in Tally. Right. Even the combined data sheet is showing all rows as Reverse Charge Invoices and Exempted Invoices.
 
Last edited:
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
The active sheet was messing with the evaluate.

Change:
VBA Code:
            .Value = Evaluate(Replace("if(#=""Not Found"",#&"" in ""&choose((" & .Offset(, OffsetAmount).Address & _
                    "=""PORTAL"")+1,""Portal"",""Tally""),#)", "#", .Address))

to:
VBA Code:
            .Value = wsDestination.Evaluate(Replace("if(#=""Not Found"",#&"" in ""&choose((" & .Offset(, OffsetAmount).Address & _
                    "=""PORTAL"")+1,""Portal"",""Tally""),#)", "#", .Address))
 
Upvote 0
Closed the work book without saving. Give me some time to check again.
 
Upvote 0
For Step #1:

VBA Code:
    Dim AsPerColumnNumber               As Long
    Dim DestinationRemarksColumnNumber  As Long
    Dim OffsetAmount                    As Long
'
    AsPerColumnNumber = 2                                                                                               ' <--- Set this to the 'As Per' column number in 'Mismatches'
'
   DestinationRemarksColumnNumber = Range(DestinationRemarksColumn & 1).Column                                          ' Convert DestinationRemarksColumn letter to a number
'
        With .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & .Range("A" & .Rows.Count).End(xlUp).Row)   '
            OffsetAmount = AsPerColumnNumber - DestinationRemarksColumnNumber
'
            .Value = wsDestination.Evaluate(Replace("if(#=""Not Found"",#&"" in ""&choose((" & .Offset(, OffsetAmount).Address & _
                    "=""PORTAL"")+1,""Portal"",""Tally""),#)", "#", .Address))
        End With

For step #2 ... see post #11
 
Upvote 0
Solution
Feeling very sleepy. The application has become a mess. Will try in the morning. Good Night JohnnyL.
 
Upvote 0
Phew!! I gave one last try and got it right. Everything as expected and the speed is also good.
See you soon .
Thank you JohnnyL.
Good Night.
 
Upvote 0

Forum statistics

Threads
1,215,507
Messages
6,125,212
Members
449,214
Latest member
mr_ordinaryboy

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