使用查找功能时出错 [英] Error while using lookup function

问题描述

我有两张纸.Sheet1:上周，sheet2:本周.

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

我正在用sheet1在sheet2的A列中查找我的ID，如果它们匹配，我会将值从sheet1的M列复制到sheet2的M列.

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.

由于某种原因，我在sheet1中找不到的值被填充为"0".我不希望这种情况发生在我的代码中.我只希望代码查找ID，如果它们匹配我想要的值，否则我不希望打印任何内容.

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

推荐答案

代替

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), "")

尝试

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

尽管可以使用工作表和范围变量来修改您的代码.并确保使用正确的 tr 和 trsh .

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

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

请参阅图片以供参考.

Sheet LastWeek

Sheet LastWeek

Sheet ThisWeek

Sheet ThisWeek

这篇关于使用查找功能时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！