Excel VBA添加按钮触发宏时遇运行时错误'13'类型不匹配

问题场景 #

编写了test宏用于将Examples工作表的数据同步到Test Summary工作表，同时用代码创建按钮绑定该宏，但无论通过代码创建还是手动添加按钮，运行时都会触发错误'13'类型不匹配，报错行如下：

Rng1.Offset(0, 0).Formula = "='" & WS2.Name & "'!" & Rng2.Cells([7], [1]).Address 'Project

错误原因及修复方案 #

1. 单元格引用语法错误 #

Rng2.Cells([7], [1])中的[7]和[1]是VBA里的单元格直接引用语法（等价于Range("7")和Range("1")），不是表示行号、列号的数值，这会导致Cells方法接收的参数类型错误，触发类型不匹配。

需要把中括号去掉，直接用数字作为行号和列号参数：

Rng2.Cells(7, 1)

2. 循环逻辑错误 #

原代码中Set Rng2 = Rng2.Offset(0, i + 1)的逻辑有问题：每次循环i会递增，导致偏移量越来越大，会跳过大量列，甚至超出工作表范围引发额外错误。应该改为每次仅向右偏移1列，遍历Examples工作表的F列后续列。

修正后的完整test宏代码 #

Sub test()
    Dim Rng1 As Range, Rng2 As Range
    Dim ws1 As Worksheet, WS2 As Worksheet, i As Long
    Set ws1 = ThisWorkbook.Worksheets("Test Summary")
    Set Rng1 = ws1.Range("E2")
    Set WS2 = ThisWorkbook.Worksheets("Examples")
    Set Rng2 = WS2.Range("F2")
    
    ' Clear original data on Summary
    i = 0
    Do Until IsEmpty(Rng1.Offset(i, 0))
        Rng1.Offset(i, 0).EntireRow.ClearContents
        i = i + 1
    Loop
    i = 0
    
    Do Until IsEmpty(Rng2.Offset(0, i))
       If Rng2.Value <> "" Then
            ' 修正Cells参数的中括号问题
            Rng1.Offset(0, 0).Formula = "='" & WS2.Name & "'!" & Rng2.Cells(7, 1).Address 'Project
            Rng1.Offset(0, 1).Formula = "='" & WS2.Name & "'!" & Rng2.Cells(9, 1).Address 'Model
            Rng1.Offset(0, 2).Formula = "='" & WS2.Name & "'!" & Rng2.Cells(10, 1).Address 'ID
            ' 修正偏移逻辑，每次仅右移1列
            Set Rng2 = Rng2.Offset(0, 1)
        End If
        Set Rng1 = Rng1.Offset(1, 0) ' Move to next row to check
        i = i + 1
    Loop
   i = 0
    ' Clean up
    Set Rng1 = Nothing
    Set ws1 = Nothing
    Set Rng2 = Nothing
    Set WS2 = Nothing
End Sub

额外说明 #

• 按钮创建代码仅需运行一次，运行后按钮会出现在Test Summary工作表上，后续直接点击按钮即可触发同步：

Sub CommandButton_Click()
    Dim b As Button
    Dim ws10 As Worksheet
    Set ws10 = ThisWorkbook.Worksheets("Test Summary")
    Set b = ws10.Buttons.Add(200, 5, 80, 18.75)       ' left, top, width, height
    b.OnAction = "test" ' a sub name in module
    b.Characters.Text = "Refresh"  ' text on the button
End Sub

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