如何用VBA为Excel多工作表的多个单元格批量赋值？代码问题求助

你的代码存在几个关键错误，导致目标单元格无法写入内容，具体问题和修正方法如下：

1. 变量类型不匹配 #

Floors数组被声明为Range类型，但你实际赋值的是Worksheet对象，类型完全不兼容。应将数组声明为Worksheet类型：

Dim Floors(5) As Worksheet

2. 未指定Range的父对象 #

Set SampleCell = .Range("M5")中的.需要依附于具体工作表对象，否则逻辑不成立；而且完全不需要提前声明这些单元格变量，直接在循环中针对当前工作表操作更清晰。

3. 无效的遍历逻辑 #

For Each SampleCell In Floors(FNo)会遍历当前工作表的所有单元格，既浪费资源又偏离需求——你只需要定位到指定单元格（M5、M25）赋值即可，不需要遍历整个工作表。

4. 未初始化的Target变量 #

SampleTarget和SampleTarget2从未用Set初始化，属于空对象，对其赋值的操作不会产生任何效果；实际上你根本不需要这两个变量，直接给目标单元格赋值即可。

修正后的代码 #

Sub AssignValuesToMultipleSheets()
    Dim Floors(5) As Worksheet, FNo As Integer
    
    ' 初始化工作表数组
    Set Floors(0) = Worksheets("Ground Floor Plan")
    Set Floors(1) = Worksheets("First Floor Plan")
    Set Floors(2) = Worksheets("Second Floor Plan")
    Set Floors(3) = Worksheets("Third Floor Plan")
    Set Floors(4) = Worksheets("Fourth Floor Plan")
    Set Floors(5) = Worksheets("Fifth Floor Plan")
    
    ' 遍历每个工作表，给指定单元格赋值
    For FNo = 0 To 5
        With Floors(FNo)
            .Range("M5").Value = "Cocolocco lusis ponton"
            .Range("M25").Value = "Other SampleCell Value"
        End With
    Next FNo
End Sub

如果需要批量处理更多单元格，可以把目标单元格地址和对应值放到数组中，循环批量赋值，示例如下：

Sub AssignValuesToMultipleCells()
    Dim Floors(5) As Worksheet, FNo As Integer
    Dim cellAddresses As Variant, cellVals As Variant
    Dim i As Integer
    
    ' 初始化工作表数组
    Set Floors(0) = Worksheets("Ground Floor Plan")
    Set Floors(1) = Worksheets("First Floor Plan")
    Set Floors(2) = Worksheets("Second Floor Plan")
    Set Floors(3) = Worksheets("Third Floor Plan")
    Set Floors(4) = Worksheets("Fourth Floor Plan")
    Set Floors(5) = Worksheets("Fifth Floor Plan")
    
    ' 定义要赋值的单元格地址和对应的值
    cellAddresses = Array("M5", "M25", "A1", "B3")
    cellVals = Array("Cocolocco lusis ponton", "Other SampleCell Value", "Hello", "World")
    
    ' 遍历工作表和单元格
    For FNo = 0 To 5
        With Floors(FNo)
            For i = LBound(cellAddresses) To UBound(cellAddresses)
                .Range(cellAddresses(i)).Value = cellVals(i)
            Next i
        End With
    Next FNo
End Sub

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