时间戳

首页 > 解决方案 > 数组 vlookup 的运行时错误类型不匹配

arrays - 数组 vlookup 的运行时错误类型不匹配

问题描述

我有遇到

运行时错误 13 类型不匹配

在以下代码运行时。基本上我想使用第 31 列中的值在另一张表中查找以返回第 32 列中的搜索值，第 33 列中的搜索日期。

请帮助我。

Sub vlookupFU()

'vlookupfollowup material & Eff-out date
Dim wkbNPI As Workbook
Dim wksPT As Worksheet
Dim wksFU As Worksheet

Set wkbNPI = ActiveWorkbook
Set wksPT = wkbNPI.Sheets("Packaging tracking")
Set wksFU = wkbNPI.Sheets("FollowUpMaterial")

Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Dim lrw2 As Long
    lrw2 = wksPT.Cells(Rows.Count, "A").End(xlUp).row

Dim PTarray As Variant
Dim i As Long, j As Long

PTarray = wksPT.Range("A7:AG" & lrw2)

Dim Oldcode As String
Dim FUM As String           'Follow up material code
Dim FUMD As String            'Follow up material date

For i = 1 To lrw2
Oldcode = PTarray(i, 31)

    If Oldcode <> 0 Then 'where I have error type mismatch

    FUM = wf.vlookup(PTarray(i, 31), wksFU.Range("B:R"), 13, False)               'vlookup follow up material
    FUMD = wf.vlookup(PTarray(i, 31), wksFU.Range("B:R"), 17, False)              'vlookup follow up material effective out date
    FUM = PTarray(i, 32)
    FUMD = PTarray(i, 33)

    End If


Next i

End Sub

标签： arraysexcelvbavlookup

解决方案

我相信以下内容会按您的预期工作，跳过空白单元格，您可以这样做：

If Oldcode <> "" Then

请参阅下面的修改代码，我还删除了lrw2代码中未使用的，另一点要考虑的是，除了变量之外，您不会将 Vlookup 的返回值放在任何地方，所以这不会添加值到你的 Excel 表？不确定这是否是您正在处理的事情......：

Sub vlookupFU()
'vlookupfollowup material & Eff-out date
Dim wkbNPI As Workbook: Set wkbNPI = ThisWorkbook
Dim wksPT As Worksheet: Set wksPT = wkbNPI.Sheets("Packaging tracking")
Dim wksFU As Worksheet: Set wksFU = wkbNPI.Sheets("FollowUpMaterial")
Dim wf As WorksheetFunction: Set wf = Application.WorksheetFunction
Dim lrw2 As Long, i As Long ', j As Long
Dim PTarray As Variant
Dim Oldcode As String, FUM As String, FUMD As String

'lrw2 = wksPT.Cells(Rows.Count, "A").End(xlUp).Row 'not used so no need for this line
PTarray = wksPT.Range("A7:AG" & lrw2)

For i = 1 To wksPT.Range("A7:AG" & lrw2).Rows.Count
    Oldcode = PTarray(i, 31)

    If Oldcode <> "" Then
        FUM = wf.VLookup(PTarray(i, 31), wksFU.Range("B:R"), 13, False)               'vlookup follow up material
        FUMD = wf.VLookup(PTarray(i, 31), wksFU.Range("B:R"), 17, False)              'vlookup follow up material effective out date
        'FUM = PTarray(i, 32) 'you get the value above and then you overwrite it here??
        'FUMD = PTarray(i, 33) 'you get the value above and then you overwrite it here??
    End If
Next i
End Sub

推荐阅读