Excel VBA执行VLOOKUP时弹出更新值窗口，提示Pivot表缺失求助

问题描述 #

我是Excel VBA初学者，运行代码时遇到以下问题：

• 使用VLOOKUP函数，查找值来自「Collection Details」工作表，查找区域位于「Pivot」工作表
• 执行对应代码行时弹出“更新值”窗口，提示「Pivot」工作表缺失
• 尝试将子程序拆分为两部分调用，问题仍未解决

引发问题的代码行 #

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Formula = "=IF(VLOOKUP($B2,Pivot!$A:$B,2,0)> Collection Details!$F2, Collection Details!$F2,VLOOKUP($B2,Pivot!$A:$B,2,0))"

完整代码 #

Option Explicit

Sub pivot()

Dim count As Integer

ThisWorkbook.Worksheets(" Collection Details").Select

ThisWorkbook.Worksheets(" Collection Details").UsedRange.EntireColumn.AutoFit

ThisWorkbook.Worksheets(" Collection Details").Range("J2").Select

count = Range(Selection, Selection.End(xlDown)).count

ThisWorkbook.Worksheets(" Collection Details").Range(Selection, Selection.End(xlDown)).Value = "X00000002"

ThisWorkbook.Worksheets(" Collection Details").Range("I2:I" & count + 1).Value = "=TODAY()"

ThisWorkbook.Worksheets(" Collection Details").Range("I2:I" & count + 1).Select

Selection.Copy

Selection.PasteSpecial xlPasteValues

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Formula = "=IF(VLOOKUP($B2,Pivot!$A:$B,2,0)> Collection Details!$F2, Collection Details!$F2,VLOOKUP($B2,Pivot!$A:$B,2,0))"

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Select

ThisWorkbook.Sheets("CN-DN Data").Select

ThisWorkbook.Worksheets("CN-DN Data").Range("A1:A9").EntireRow.Delete

ThisWorkbook.Worksheets("CN-DN Data").UsedRange.EntireColumn.AutoFit

ThisWorkbook.Worksheets("CN-DN Data").Cells(1, 1).Select

Sheets("Pivot").Select

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="CN-DN Data!R1C1:R1048576C15", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Sales Return Bill No").Orientation = xlRowField
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Sales Return Bill No").Position = 1

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").AddDataField ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Pending Amt"), "Sum of Pendig Amt", xlSum

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").Orientation = xlPageField
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").Position = 1

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").PivotItems("From Sales Return").Visible = True
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").PivotItems("From Market Return").Visible = False
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").PivotItems("(blank)").Visible = False


End Sub

Sub Cn_Adjust()
'
'Cn_Adjust Macro
'
' Keyboard Shortcut: Ctrl Shift + Q
'
Dim count As Integer


ThisWorkbook.Worksheets(" Collection Details").Select

ThisWorkbook.Worksheets(" Collection Details").UsedRange.EntireColumn.AutoFit



Call pivot


ThisWorkbook.Worksheets(" Collection Details").Range("A1:O" & count + 2).AutoFilter Field:=7, Criteria1:="#N/A"

ThisWorkbook.Worksheets(" Collection Details").Range("A2:O" & count + 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ThisWorkbook.Worksheets(" Collection Details").AutoFilterMode = False

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Select

Selection.Copy

Selection.PasteSpecial xlPasteValues


End Sub

工作表信息 #

包含三个工作表：Collection Details、CN-DN Data、Pivot

问题原因与解决方法 #

1. 核心原因：代码执行顺序错误 #

你先设置了依赖「Pivot」工作表的VLOOKUP公式，之后才创建「Pivot」的数据透视表。此时「Pivot!$A:$B」区域无数据，Excel判定引用缺失，弹出更新窗口。

解决：调整代码顺序
将设置G列公式的代码，移到创建完数据透视表的代码之后：

' pivot子程序中，把原设置公式的代码剪切到此处（创建透视表的代码之后）
ThisWorkbook.Worksheets("Collection Details").Range("G2:G" & count + 1).Formula = "=IF(VLOOKUP($B2,Pivot!$A:$B,2,0)>$F2,$F2,VLOOKUP($B2,Pivot!$A:$B,2,0))"

2. 次要问题：工作表名称多余空格 #

代码中「Collection Details」名称前有多余空格（" Collection Details"），可能导致引用匹配失败。
解决：清理空格
将所有" Collection Details"改为"Collection Details"。

3. 潜在问题：count变量作用域 #

Cn_Adjust中声明的count未赋值，后续使用会引发错误。
解决：设置模块级变量
在模块顶部声明变量，让两个子程序共享：

' 模块顶部添加
Dim count As Integer

Sub pivot()
    ' 移除此处的Dim count As Integer
    ' ... 原有代码 ...
End Sub

Sub Cn_Adjust()
    ' 移除此处的Dim count As Integer
    ' ... 原有代码 ...
End Sub

4. 优化建议：去掉不必要的Select操作 #

直接引用对象，提升代码效率和稳定性：

With ThisWorkbook.Worksheets("Collection Details")
    .UsedRange.EntireColumn.AutoFit
    count = .Range("J2", .Range("J2").End(xlDown)).Count
    .Range("J2:J" & count + 1).Value = "X00000002"
    .Range("I2:I" & count + 1).Value = Date ' 替代TODAY()公式+粘贴值
End With

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