excel 工作表函数Vlookup不返回任何内容

mwecs4sa  于 2023-08-08  发布在  其他
关注(0)|答案(1)|浏览(124)

我试图创建一个宏,如果在主数据表中找到产品,则返回“有效”,如果没有找到,则返回“无效”。我正在使用工作表函数vlookup来完成此操作。
下面是代码:

Sub fg_type()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim wb As Workbook
Dim ws As Worksheet
Dim ref_wb As Workbook
Dim ref_ws As Worksheet
Dim lastRow As Long
Dim ref_lastRow As Long

Dim lookup_val As Variant
Dim table_arr As Variant
Dim fgtype As Variant

Set wb = Workbooks("un_orders.xlsm")
Set ws = wb.Worksheets("uo")
Set ref_wb = Workbooks("target.xlsx")
Set ref_ws = ref_wb.Worksheets("Document")

lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
ref_lastRow = ref_ws.Cells(ref_ws.Rows.Count, "G").End(xlUp).Row

table_arr = ref_ws.Range("G3:G" & ref_lastRow)

For i = 2 To lastRow
    lookup_val = ws.Range("C" & i).Value
    On Error Resume Next
    Err.Clear
    fgtype = Application.WorksheetFunction.VLookup(lookup_val, table_arr, 1, False)
    If Err.Number = 0 Then
        fgtype(i, 1) = "valid"
    Else
        fgtype(i, 1) = "invalid"
    End If
    ws.Range("A" & i).Value = fgtype(i, 1)
Next i

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

字符串
但是,在运行代码之后,所有返回都是空的。我做错了什么?

juud5qan

juud5qan1#

试试这个:

If Err.Number = 0 Then
        ws.Range("A" & i).Value = "valid"
    Else
        ws.Range("A" & i).Value = "invalid"
    End If
Next i
On Error Goto 0

字符串
在不必要的时候总是禁用自定义错误处理,因为它会隐藏错误消息,正如Notus_Panda评论的那样。

相关问题