Excel VBA循环调用单元格报错1004：Range方法调用失败求助

问题背景 #

主工作表数据如下：

France    10
Germany   14
US        20

另有三个分别名为France、Germany、US的工作表。需要实现：

• 将主表每行的数值复制到对应名称的工作表中
• 目标单元格由主表O1单元格定义（O1内容为=B5，即目标单元格是对应工作表的B5）
• 循环次数由主表P1单元格指定（P1值为3）

编写的VBA代码在循环时，标记**的行出现error 1004: method "range" of object "global" failed错误，单独测试变量赋值正常。

原代码：

Sub trial()
Dim destination As String
Dim inputer As Long
Dim country As String
Dim counter As Boolean
Dim maxcounter As Boolean

maxcounter = Range("P1").Value

counter = "1"

While maxcounter > counter:

  destination = Range("O1").Value

    **country = Range("A" & counter).Value**

    inputer = Range("B" & counter).Value

    Sheets(country).Range(destination).Value = inputer

    counter = counter + 1
Wend

End Sub

错误原因 #

最核心的问题是把循环计数变量counter和maxcounter定义成了Boolean（布尔类型）。布尔类型只有True（对应数值-1）和False（对应数值0）两个取值，用来做循环计数完全不符合逻辑，导致Range("A" & counter)生成的单元格引用完全错误，触发1004报错。

修正后的代码 #

Sub trial()
    Dim destination As String
    Dim inputer As Long
    Dim country As String
    Dim counter As Integer
    Dim maxcounter As Integer

    ' 明确指定主工作表，替换成你的主表实际名称
    With ThisWorkbook.Worksheets("主工作表")
        maxcounter = .Range("P1").Value
        ' 提取O1的显示值作为目标单元格地址
        destination = .Range("O1").Value
        counter = 1

        ' 修正循环条件，确保遍历所有指定行
        While counter <= maxcounter
            country = .Range("A" & counter).Value
            inputer = .Range("B" & counter).Value
            
            ' 先检查目标工作表是否存在，避免报错
            If SheetExists(country) Then
                ThisWorkbook.Worksheets(country).Range(destination).Value = inputer
            Else
                MsgBox "工作表 " & country & " 不存在，请检查！"
            End If
            
            counter = counter + 1
        Wend
    End With
End Sub

' 辅助函数：检查指定名称的工作表是否存在
Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

关键修正说明 #

1. 变量类型修正：将counter和maxcounter改为Integer（或Long，如果数据行数较多），确保计数逻辑正常。
2. 明确工作表引用：用With语句绑定主工作表，避免代码运行时因当前激活工作表变化导致的引用错误。
3. 循环条件修正：把maxcounter > counter改为counter <= maxcounter，避免漏掉最后一行数据（当maxcounter=3时，原条件会跳过counter=3的情况）。
4. 添加错误防护：新增SheetExists辅助函数检查目标工作表是否存在，避免因工作表名称拼写错误导致的额外报错。
5. 初始化优化：直接用数值1初始化counter，不用字符串"1"，避免不必要的类型转换。

内容的提问来源于stack exchange，提问作者Filip Smrekar Apih