VBA中带变量的VLOOKUP公式报错问题排查求助

我来帮你排查下lastcol变量异常的问题，顺便把VLOOKUP公式的引用也给你理顺清楚。你的核心问题出在**UsedRange的不可靠性**，以及公式字符串里的格式错误，咱们一步步来解决：

一、为什么lastcol会出现异常？ #

你当前用lastcol = VisualWS.UsedRange.Columns.Count来获取最后一列，这个方法的问题在于：

• UsedRange会记录工作表中曾经被编辑过的所有单元格，哪怕你后来清空了这些单元格的内容，它的范围依然会包含这些区域。比如之前用过第13列，后来清空了，UsedRange.Columns.Count还是会返回13而非预期的12。
• 如果工作表完全没有数据，UsedRange会是空的，导致lastcol变成空值，直接引发运行时错误1004。

二、修复方案 #

1. 替换lastcol的获取方式，用更可靠的方法 #

换成从表头行（假设是第1行）的最右侧往左找最后一个有数据的列，这样能准确获取当前实际使用的最后一列：

' 替换initialize中的lastcol赋值行
lastcol = VisualWS.Cells(1, VisualWS.Columns.Count).End(xlToLeft).Column

如果你的数据表头不在第1行，把Cells(1, ...)里的1改成实际表头所在的行号即可。

2. 修正VLOOKUP公式的格式错误 #

原公式里的R " & LR & " C " & lastcol & "存在多余空格，R1C1格式的引用里不能有空格，这也是导致1004错误的诱因之一。同时建议用VisualWS.Name动态引用工作表名称，避免硬编码visual!：

' 替换Analyze_1中的公式行
.Range("M2:M" & LR_Over).FormulaR1C1 = "=VLOOKUP(RC[-12]," & VisualWS.Name & "!R2C1:R" & LR & "C" & lastcol & "," & lastcol & ",FALSE)"

3. 优化变量类型，避免潜在溢出 #

虽然Integer类型最大能到32767，足够覆盖Excel的列数（最多16384列），但为了避免空值或异常值导致的类型不匹配，建议把lastcol的类型从Integer改成Long：

' 修改全局变量声明
Public lastcol As Long

三、修改后的完整代码 #

全局变量声明 #

Public myExtension As String
Public FullPath As String
Public VisualWB As Workbook
Public VisualWS As Worksheet
Public LR As Long
Public lastcol As Long ' 改成Long类型
Public MonCol As Integer
Public Table As Range
Public SigilDes As Integer
Public LR_Over As Long
Public MainWB As Workbook ' 补充声明全局变量，避免隐式声明
Public ListsWS As Worksheet
Public OverWS As Worksheet
Public DoubleWS As Worksheet

initialize子过程 #

Sub initialize()
    Set MainWB = ThisWorkbook
    Path = ThisWorkbook.Path
    Set ListsWS = MainWB.Worksheets("Lists")
    Set VisualWS = MainWB.Worksheets(4)
    Set OverWS = MainWB.Worksheets(2)
    Set DoubleWS = MainWB.Worksheets(3)
    MonthName = UserForm1.ListOfMonths.Value
    MainWB.Worksheets(1).Range("F2").Value = MonthName
    
    ' 可靠获取最后一列
    lastcol = VisualWS.Cells(1, VisualWS.Columns.Count).End(xlToLeft).Column
    LR = VisualWS.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 新增异常判断，提前拦截无数据的情况
    If lastcol = 0 Or LR < 2 Then
        MsgBox "Visual工作表中没有有效数据，请检查！", vbExclamation
        Exit Sub
    End If
End Sub

Analyze_1子过程 #

Sub Analyze_1()
    Call initialize
    ' 先判断initialize是否因异常退出
    If lastcol = 0 Or LR < 2 Then Exit Sub
    
    With OverWS
        LR_Over = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("M1").Value = "workdays"
        ' 修正后的VLOOKUP公式
        .Range("M2:M" & LR_Over).FormulaR1C1 = "=VLOOKUP(RC[-12]," & VisualWS.Name & "!R2C1:R" & LR & "C" & lastcol & "," & lastcol & ",FALSE)"
    End With
End Sub

额外提示 #

建议在模块顶部添加Option Explicit，这样能强制声明所有变量，避免因隐式声明导致的变量名拼写错误或类型问题，让代码更健壮。

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