跨工作簿VLookup的VBA代码异常排查：返回表头而非匹配值的问题

我需要编写一个VLookup VBA过程，实现以下功能：在Dataset工作簿中查找当前工作簿（Daily Shout End）内的账号（Account Number），并将Dataset工作簿中的Connection ID值返回到当前工作簿的HET Lead Account列。我已编写了循环执行VLookup的代码，循环将持续到数据最后一行。代码中b、c、d、e代表VLookup公式所使用的列字母与列号，VLValue代表VLookup的列索引（Col Index）。但运行该代码时，指定范围内的所有单元格均返回Dataset工作簿中的表头“Connection ID”，我无法定位问题根源，恳请帮忙排查代码问题。

My Code: #

Sub HETVLookup()
 Dim b As Range
 Dim c As Range
 Dim d As Range
 Dim e As Range
 Dim AccNoValue As Integer
 Dim AccColumnNumber As Long
 Dim AccColumnLetter As String
 Dim AccDSColumnNumber As Long
 Dim AccDSColumnLetter As String
 Dim HETColumnNumber As Long
 Dim HETColumnLetter As String
 Dim i As Long
 Dim LastRow As Long
 Dim VLValue As Long
 Dim VLRes As String
 LastRow = ThisWorkbook.Worksheets("Daily Shout End").Cells(Rows.Count, "A").End(xlUp).Row
 DatasetFile = Dir(pStr & "Dataset*.xlsx")
 With ThisWorkbook.Worksheets("Daily Shout End").Rows(1)
 Set b = .Find("HET Lead Account", LookIn:=xlValues)
 HETMRColumnNumber = b.Column
 'Convert To Column Letter
 HETMRColumnLetter = Split(Cells(1, HETMRColumnNumber).Address, "$")(1)
 If Not b Is Nothing Then
 HETMRColumnNumber = b.Column
 End If
 End With
 'finding the column number and letter for account number in daily shout file
 With ThisWorkbook.Worksheets("Daily Shout End").Rows(1)
 Set c = .Find("Account Number", LookIn:=xlValues)
 AccColumnNumber = c.Column
 'Convert To Column Letter
 AccColumnLetter = Split(Cells(1, AccColumnNumber).Address, "$")(1)
 If Not c Is Nothing Then
 AccColumnNumber = c.Column
 End If
 End With
 'finding the column letter and number for HET Lead in the Dataset file (connection ID)
 With Workbooks(DatasetFile).Worksheets(1).Rows(1)
 Set d = .Find("Connection ID", LookIn:=xlValues)
 HETColumnNumber = d.Column
 'Convert To Column Letter
 HETColumnLetter = Split(Cells(1, HETColumnNumber).Address, "$")(1)
 If Not d Is Nothing Then
 HETNumberCol = d.Column + 1
 End If
 End With
 With Workbooks(DatasetFile).Worksheets(1).Rows(1)
 Set e = .Find("Account number", LookIn:=xlValues)
 AccDSColumnNumber = e.Column
 'Convert To Column Letter
 AccDSColumnLetter = Split(Cells(1, AccDSColumnNumber).Address, "$")(1)
 If Not e Is Nothing Then
 AccDSNumberCol = e.Column + 1
 End If
 End With
 VLValue = HETColumnNumber - AccDSColumnNumber + 1
 For i = 2 To LastRow
 ThisWorkbook.Worksheets("Daily Shout End").Cells(i, HETMRColumnNumber).Value2 = _
 Application.VLookup(AccColumnLetter & i, Workbooks("Dataset 20210127.xlsx").Worksheets(1).Range(AccDSColumnLetter _
 & ":" & HETColumnLetter), VLValue)
 Next i
End Sub

Problem Analysis & Fixes #

Let’s break down why you’re getting the header value instead of matching results:

1. Incorrect Lookup Value
   The biggest issue is your VLookup’s first parameter: AccColumnLetter & i creates a string like "A2", but VLookup needs the value inside that cell, not the cell address as text. Since your Dataset’s account column contains numbers (not strings like "A2"), VLookup can’t find a match. When you omit the 4th range_lookup parameter, it defaults to True (approximate match), which falls back to the first item in the range—the header row—and returns the corresponding "Connection ID" text.

2. Hardcoded Dataset File Name
   You’re using Workbooks("Dataset 20210127.xlsx") but earlier you stored the correct filename in DatasetFile—this will break if the Dataset filename changes.

3. Unnecessary Variables & Missing Error Checks
   Some variables (like AccNoValue, VLRes, HETNumberCol) are unused. Also, if the Find method fails to locate a column header, your code will throw an error because you’re trying to access .Column on a Nothing range.

4. Missing Exact Match Parameter
   Always specify False as the 4th VLookup parameter for exact matches—this prevents unexpected approximate matches.

Corrected Code #

Sub HETVLookup()
    Dim wsDaily As Worksheet
    Dim wsDataset As Worksheet
    Dim rngHETHeader As Range
    Dim rngAccHeader As Range
    Dim rngDSConnHeader As Range
    Dim rngDSAccHeader As Range
    Dim LastRow As Long
    Dim VLValue As Long
    Dim DatasetFile As String
    Dim pStr As String ' Make sure this variable is defined/set elsewhere
    
    ' Set references to worksheets for cleaner code
    Set wsDaily = ThisWorkbook.Worksheets("Daily Shout End")
    DatasetFile = Dir(pStr & "Dataset*.xlsx")
    If DatasetFile = "" Then
        MsgBox "Dataset file not found!", vbExclamation
        Exit Sub
    End If
    Set wsDataset = Workbooks(DatasetFile).Worksheets(1)
    
    ' Find HET Lead Account column in Daily Shout End
    Set rngHETHeader = wsDaily.Rows(1).Find("HET Lead Account", LookIn:=xlValues, LookAt:=xlWhole)
    If rngHETHeader Is Nothing Then
        MsgBox "HET Lead Account column not found!", vbExclamation
        Exit Sub
    End If
    
    ' Find Account Number column in Daily Shout End
    Set rngAccHeader = wsDaily.Rows(1).Find("Account Number", LookIn:=xlValues, LookAt:=xlWhole)
    If rngAccHeader Is Nothing Then
        MsgBox "Account Number column not found!", vbExclamation
        Exit Sub
    End If
    
    ' Find Connection ID column in Dataset
    Set rngDSConnHeader = wsDataset.Rows(1).Find("Connection ID", LookIn:=xlValues, LookAt:=xlWhole)
    If rngDSConnHeader Is Nothing Then
        MsgBox "Connection ID column not found in Dataset!", vbExclamation
        Exit Sub
    End If
    
    ' Find Account number column in Dataset
    Set rngDSAccHeader = wsDataset.Rows(1).Find("Account number", LookIn:=xlValues, LookAt:=xlWhole)
    If rngDSAccHeader Is Nothing Then
        MsgBox "Account number column not found in Dataset!", vbExclamation
        Exit Sub
    End If
    
    ' Calculate VLookup column index
    VLValue = rngDSConnHeader.Column - rngDSAccHeader.Column + 1
    LastRow = wsDaily.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Loop through rows and perform VLookup
    Dim i As Long
    For i = 2 To LastRow
        ' Use cell VALUE as lookup parameter, not address string
        Dim lookupValue As Variant
        lookupValue = wsDaily.Cells(i, rngAccHeader.Column).Value
        
        ' Handle empty lookup values to avoid errors
        If Not IsEmpty(lookupValue) Then
            wsDaily.Cells(i, rngHETHeader.Column).Value2 = _
                Application.VLookup(lookupValue, wsDataset.Range(rngDSAccHeader, wsDataset.Cells(wsDataset.Rows.Count, rngDSConnHeader.Column)), VLValue, False)
        Else
            wsDaily.Cells(i, rngHETHeader.Column).Value = ""
        End If
    Next i
End Sub

Key Changes Explained: #

• Replaced cell address strings with actual cell values for the lookup parameter
• Added error checks for missing columns/files to avoid runtime errors
• Used worksheet variables (wsDaily, wsDataset) for cleaner, more maintainable code
• Specified False for exact match in VLookup
• Removed unused variables
• Handled empty lookup values to prevent unnecessary VLookup calls

内容的提问来源于stack exchange，提问作者user11091170