Excel VBA Vlookup复制其他表中不存在的条目的先前条目数据 [英] Excel VBA Vlookup copying previous entry data for entries not present in other sheet

问题描述

我正在使用以下代码使用vlookup将数据从第一张纸传输到第二张纸。问题是第一张表中不存在数据的第二张表条目正在填充第二张表的先前条目。有人可以帮我理解我的错误吗？

I am using following code to bring data from 1st sheet to 2nd using vlookup. The problem is 2nd sheet entries for which data is not present in 1st sheet are getting populated with previous entries of 2nd sheet. Can someone please help me understand my mistake?

Dim NIMsLastRow As Integer
Dim NIMsLastCol As Integer
Dim tempInt As Integer
Dim temp3 As String
Dim tempin As String
Dim ColLtr As String
NIMsLastRow = Worksheets("NIMSCarrierCount").Cells(Rows.Count, 1).End(xlUp).Row
NIMsLastCol = Worksheets("NIMSCarrierCount").Cells(1, Columns.Count).End(xlToLeft).Column
AudLastRow = Worksheets("Audit-NIMS vs Site Topology").Cells(Rows.Count, 1).End(xlUp).Row
ColLtr = Replace(Cells(1, NIMsLastCol).Address(True, False), "$1", "")
For i = 2 To NIMsLastCol
    For j = 1 To AudLastRow
        On Error Resume Next
        tempin = Worksheets("Audit-NIMS vs Site Topology").Cells(j, 1).Value
        temp3 = Application.WorksheetFunction.VLookup(tempin, Worksheets("NIMSCarrierCount").Range("A" & 1 & ":" & ColLtr & NIMsLastRow), i, False)
        If IsError(temp3) Then
            Cells(j, i).Value = "NA"
        Else
            Cells(j, i).Value = temp3
        End If
    Next j
Next i

推荐答案

尝试以下操作：

Option Explicit

Sub test()

    Dim NIMsLastRow As Long
    Dim NIMsLastCol As Long
    Dim temp3 As Variant 'see change here
    Dim tempin As String
    Dim ColLtr As String
    Dim AudLastRow As Long

    NIMsLastRow = Worksheets("NIMSCarrierCount").Cells(Rows.Count, 1).End(xlUp).Row
    NIMsLastCol = Worksheets("NIMSCarrierCount").Cells(1, Columns.Count).End(xlToLeft).Column
    AudLastRow = Worksheets("Audit-NIMS vs Site Topology").Cells(Rows.Count, 1).End(xlUp).Row
    ColLtr = Replace(Cells(1, NIMsLastCol).Address(True, False), "$1", "")

    Dim j As Long

    For j = 1 To NIMsLastRow

        On Error Resume Next
        tempin = Worksheets("Audit-NIMS vs Site Topology").Cells(j, 1).Value
        temp3 = Application.Match(tempin, Worksheets("NIMSCarrierCount").Range("A1:A" & NIMsLastRow), 0)

        If IsError(temp3) Then

             Cells(j, 2).Resize(1, NIMsLastCol - 1) = "NA"
        Else
             Cells(j, 2).Resize(1, NIMsLastCol - 1).Value = Worksheets("NIMSCarrierCount").Range("B" & temp3 & ":" & ColLtr & temp3).Value
        End If

        On Error GoTo 0

    Next j

End Sub

这篇关于Excel VBA Vlookup复制其他表中不存在的条目的先前条目数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！