Need to edit / correct code

[RAJESH1960]

Hello JohnnyL
I have come across one problem in the Match Portal with Books code. When there is no GSTIN number in the other sheets (Purchases, Journal) in the last row but the other columns have data, the code is not shifting the row to the mismatched Sheet. Actually, it is not even taking into consideration that particular row and doing nothing.
First test the code as it is. I have given the wrong GSTIN number to the last row in Purchases and Journal. The 2 rows with the wrong GSTIN will display in the mismatched sheet.
Now delete the GSTIN number in the cell of the last row of both Purchases and Journal. This time it will not show in Mismatches nor Matched and not even in the combined portal sheet.
Likewise, if I delete the last 2 or more GSTIN numbers in the cell then all those rows need to display in the Mismatched sheet. which it is not.
I think the code is only taking the rows till the last GSTIN number.
Need your help to correct this.
One fault to rectify.xlsm

 

[Alex Blakenburg]

The code is mostly using the xlUp technique of finding the last row and using the GSTIN column as the reference column.

If you are going to have lines that you want to take into account with a Blank GSTIN, you would need to find another way to work out what the last row is.
Is there another column that is guaranteed to always have data eg the Item column for all the sheets that xlUp on Column B ?
If so then simply looks for xlUp and switch where ever you see "B" on that line to "A".
There are I think 2 sheets that use column "A" but I don't think they are causing you and issue.

 

[RAJESH1960]

Let me try it out.

 

[RAJESH1960]

I can't find column B xlup. What I found is already A.

SourceLastRow = wsSource.Range("A" & Rows.Count).End(xlUp).Row                          ' Get last row used in column A of the source sheeet

 

[RAJESH1960]

Set wsSource = Sheets(SourceSheet)

Ws Source are all other sheets except
"Conditions", "2A", "Matched" and "Mismatches"

The code is mostly using the xlUp technique of finding the last row and using the GSTIN column as the reference column.

If you are going to have lines that you want to take into account with a Blank GSTIN, you would need to find another way to work out what the last row is.
Is there another column that is guaranteed to always have data eg the Item column for all the sheets that xlUp on Column B ?
If so then simply looks for xlUp and switch where ever you see "B" on that line to "A".
There are I think 2 sheets that use column "A" but I don't think they are causing you and issue.

 

[RAJESH1960]

This is the new corrected link in which one issue is pending to be corrected.
Corrected One fault to rectify.xlsm

 

[Alex Blakenburg]

In your example in the initial post you referred to sheets (Purchases, Journal).
Both of those are using the code below to determine the last row.
Change that to "A" and see if that solves your issue.

Sub GetDataFromDataSheet(DataWorkSheet As String)
'
    Dim ArrayColumn                 As Long, ArrayRow       As Long
    Dim DataLastColumn              As String, DataLastRow  As Long, DestinationStartRow    As Long
    Dim CorrectedDataArray          As Variant
    Dim DataSheetArray              As Variant
'
    With Sheets(DataWorkSheet)
        DataLastRow = .Range("B" & .Rows.Count).End(xlUp).Row                                       ' Get last row of the Data sheet column B
'

 

[RAJESH1960]

In your example your initial post you referred to sheets (Purchases, Journal).
Both of those are using the code below to determine the last row.
Change that to "A" and see if that solves your issue.

Sub GetDataFromDataSheet(DataWorkSheet As String)
'
    Dim ArrayColumn                 As Long, ArrayRow       As Long
    Dim DataLastColumn              As String, DataLastRow  As Long, DestinationStartRow    As Long
    Dim CorrectedDataArray          As Variant
    Dim DataSheetArray              As Variant
'
    With Sheets(DataWorkSheet)
        DataLastRow = .Range("B" & .Rows.Count).End(xlUp).Row                                       ' Get last row of the Data sheet column B
'

Ok. I am out. I will try it out once I reach home. Will update you.

 

[RAJESH1960]

DataLastRow = .Range("B" & .Rows.Count).End(xlUp

That is perfect. Thanks once again Alex Blakenburg

 

[Alex Blakenburg]

No problem. Glad I could help.