通过VBA更新筛选后Excel工作表中的单元格值

你的问题出在直接对SpecialCells(xlCellTypeVisible)返回的区域使用Offset——筛选后的可见区域通常是不连续的，这种操作会导致偏移到非目标列或非筛选行。以下是修正后的代码，可实现逐个更新筛选后数据的E列值，匹配你每周循环填充日期的逻辑：

Sub Macro3()
    Dim sh As Worksheet
    Dim visibleCells As Range
    Dim cell As Range
    Dim area As Range
    Dim fillCount As Integer
    Dim weeklyCommCount As Integer
    Dim repeateDate As Date
    Dim i As Integer, j As Integer
    
    Set sh = ThisWorkbook.Sheets("TempSheet")
    weeklyCommCount = InputBox("Enter Weekly Communication Count (in number)")
    repeateDate = CDate(InputBox("Enter Planning Start Date ('DD-MMM-YY')", "CHR Planning", "01-Apr-24"))
    
    ' 获取E列筛选后的可见数据行（排除表头E1）
    On Error Resume Next ' 防止没有可见单元格时报错
    Set visibleCells = sh.Range("E2:E" & sh.Cells(sh.Rows.Count, "E").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If visibleCells Is Nothing Then
        MsgBox "没有筛选出可见数据行"
        Exit Sub
    End If
    
    fillCount = 0
    For i = 1 To 40
        For j = 1 To weeklyCommCount
            ' 遍历每个可见区域的单元格，逐个填充日期
            For Each area In visibleCells.Areas
                For Each cell In area.Cells
                    If fillCount < (i - 1) * weeklyCommCount + j Then
                        cell.Value = repeateDate
                        fillCount = fillCount + 1
                        Exit For
                    End If
                Next cell
                If fillCount >= (i - 1) * weeklyCommCount + j Then Exit For
            Next area
            ' 若已填满所有可见单元格，提前结束程序
            If fillCount >= visibleCells.Count Then
                MsgBox "已填满所有可见单元格，提前结束"
                Exit Sub
            End If
        Next j
        repeateDate = repeateDate + 7
    Next i
End Sub

关键修正说明： #

• 精准锁定可见区域：指定从E2开始到E列最后一行的范围再筛选可见单元格，避免误操作表头或无效区域
• 处理不连续区域：通过For Each area In visibleCells.Areas遍历每个连续的可见块，再逐个单元格赋值，确保只修改筛选后的行
• 错误防护：添加错误处理逻辑，避免无可见单元格时程序崩溃
• 匹配填充逻辑：严格遵循你设定的“每周循环40次、每周填充N次相同日期”的规则，逐次分配到可见单元格中

如果你的需求是给所有筛选后的E列单元格统一赋值，可简化为：

' 直接给所有可见E列数据行赋值
visibleCells.Value = repeateDate

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