Issue while fetching data by comparing IDs from two different sheets in Excel VBA. Gives incorrect data

Question

screenshot-fetching week 1 salaryI have written VBA code in excel to fetch driver weekly data in single master payment sheet. I use Driver ID has primary key to to fetch driver data. There are total 4 weeks reports MCMSSummaryReport(Week1), MCMSSummaryReport(Week2), MCMSSummaryReport(Week3),MCMSSummaryReport(Week4).

I am trying to fetch data in sheet "Monthly Payment Master2" by comparing driver ID. "Monthly Payment Master2" has list of driver id. I compare Monthly Payment Master2's driver id with other 4 weekly reports.

however when code does not find same id in weekly report which is present in Monthly Payment Master2 table it should return "" (blank) in column 'Week1'. It returns the blank where Ids does not match but after that loop skip a row and fetch data from 1+1 row.

unable to fix this issue in the code.

Below is the excel macro enable sheet link : https://drive.google.com/open?id=1aaidUeED7rkXaw-rMHoMK-4TNzkUJlN4

below is the code :

    Private Sub CommandButton1_Click()

Dim salary As String, fromdate As String
Dim lastcoluns As Long, lastrow As Long, erow As Long, ecol As Long, lastrow1 As Long
lastcoluns = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
lastrow1 = Sheet7.Cells(Rows.Count, 1).End(xlUp).Row + 1

MsgBox (lastrow1)


Dim i As Integer
i = 2
Do While i < lastrow1
    temp1 = Worksheets("Monthly Payment Master2").Cells(i, 1)
    For j = 2 To lastrow + 1
        temp2 = Worksheets("MCMSSummaryReport(week 1)").Cells(j, 1)
        If temp1 = temp2 Then
            salary = Sheet1.Cells(i, 18).Value
            Worksheets("Monthly Payment Master2").Cells(i, 7) = salary

        Else

        End If
    Next j
    i = i + 1
Loop
MsgBox ("Week-1 data submitted successfully, Please submit Week-2 Data.")
Application.CutCopyMode = False
Sheet6.Columns().AutoFit
Range("A1").Select

End Sub

Answer 1

I would suggest changing the architecture of your loop to make it easier to read and more robust:

Dim salary As String
Dim wsMaster As Worksheet, wsReport As Worksheet
Set wsMaster = ThisWorkbook.Worksheets("Monthly Payment Master2")
Set wsReport = ThisWorkbook.Worksheets("MCMSSummaryReport(week 1)")

lastrow1 = wsMaster.Cells(Rows.Count, 1).End(xlUp).Row
lastrow2 = wsReport.Cells(Rows.Count, 1).End(xlUp).Row

Dim i As Long
Dim foundRange As Range

Dim temp1 As String

For i = 2 To lastrow
    temp1 = wsMaster.Cells(i, 1).Value2

    Set foundRange = wsReport.Range("A2:A" & lastrow2).Find(temp1, LookAt:=xlWhole, MatchCase:=True)

    If foundRange Is Nothing Then
        salary = vbNullString
    Else
        salary = foundRange.Offset(0, 17).Value2
    End If
    wsMaster.Cells(i, 7) = salary
Next i

Please note that you aren't using lastcoluns, fromdate, ecol and erow. Also you should refer to your worksheets consistently, either use Sheet1 or Worksheets("Name"), but don't use both for the same worksheet since it's confusing to other readers.