Error while using lookup function

Question

I am having two Sheets . Sheet1 : Last week, and sheet2 : This week.

I am looking for my ID in column A of sheet2 with sheet1, and if they match, I am copying the values from column M of sheet1 to column M of sheet2.

Due to some reason, the values that i dont find in sheet1 are getting filled as "0". I dont want this to occur, with my code. I just want the code to look for ID, if they match i want the value, else i dont want anything to be printed.

Could someone suggest Where i am going wrong ?

Sub lookup()
Dim tr As Long
Dim trsh As Long
tr = Sheets("ThisWeek").Cells(Rows.Count, "A").End(xlUp).Row
trsh = Sheets("ThisWeek").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("ThisWeek").Range("M2:M" & tr).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("ThisWeek").Range("A2:A" & trsh), Sheets("LastWeek").Range("$A:$P"), 13, 0), "")
End Sub

Answer 1

The code would be like this.

Sub test()
    Dim Ws As Worksheet, toWs As Worksheet
    Dim vDB, vR(), vDB2
    Dim i As Long, j As Long

    Set toWs = Sheets("ThisWeek")
    Set Ws = Sheets("LastWeek")

    vDB = Ws.Range("a1").CurrentRegion
    vDB2 = toWs.Range("a1").CurrentRegion

    n = UBound(vDB2, 1)
    ReDim vR(1 To n - 1, 1 To 1)
    For i = 2 To n
        For j = 2 To UBound(vDB, 1)
            If vDB2(i, 1) = vDB(j, 1) Then
                vR(i - 1, 1) = vDB(j, 13)
                Exit For
            End If
        Next j
    Next i
    toWs.Range("m2").Resize(n - 1) = vR

End Sub

Answer 2

Instead of

Sheets("ThisWeek").Range("M2:M" & tr).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("ThisWeek").Range("A2:A" & trsh), Sheets("LastWeek").Range("$A:$P"), 13, 0), "")

try

Dim cel as Range
For Each cel In Sheets("ThisWeek").Range("M2:M" & tr)
    cel.Offset(0, 1).Formula = Application.WorksheetFunction.IfError(Application.VLookup(cel, Sheets("LastWeek").Range("$A:$P"), 13, 0), "")
Next cel

Though your code can be modified using worksheet and range variable. And make sure you use correct tr and trsh.

EDIT:

---

Sub lookupPSQM()
    Dim thisWeekLR As Long, lastWeekLR As Long
    Dim thisWeekSht As Worksheet, lastWeekSht As Worksheet
    Dim rng As Range, cel As Range

    Set thisWeekSht = ThisWorkbook.Sheets("ThisWeek")
    Set lastWeekSht = ThisWorkbook.Sheets("LastWeek")

    thisWeekLR = thisWeekSht.Cells(Rows.Count, "A").End(xlUp).Row
    'lastWeekLR = lastWeekSht.Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = thisWeekSht.Range("A2:A" & thisWeekLR)

    For Each cel In rng
        cel.Offset(0, 12).Formula = Application.WorksheetFunction.IfError(Application.VLookup(cel, Sheets("LastWeek").Range("$A:$P"), 13, 0), "")
    Next cel
End Sub

See image for reference.

Sheet LastWeek

Sheet ThisWeek

Answer 3

You may try something like this...

Please correct the sheet reference if required. Currently it assumes that the sheets are called as ThisWeek and LastWeek.

Sub lookupPSQM()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim tr As Long

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set wsSource = Sheets("LastWeek")
Set wsDest = Sheets("ThisWeek")

tr = wsDest.Cells(Rows.Count, "A").End(xlUp).Row

wsDest.Range("M2:M" & tr).Formula = "=IfError(VLookup(A2,'" & wsSource.Name & "'!A:M, 13, 0), """")"

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub