时间戳

首页 > 解决方案 > 用平均值替换多个工作表中的空白的代码

excel - 用平均值替换多个工作表中的空白的代码

问题描述

嗨，我有这段代码，它将遍历并用平均值替换我的工作表中的空白，我需要它来遍历大多数工作表，但它并没有遍历所有它目前只执行 1。

Sub FillBlanks()

Dim ws As Worksheet
    For Each ws In Worksheets


Dim rng1 As Range
Dim rng2 As Range

             Set rng2 = Range("L1:AB40")

             On Error Resume Next
             Set rng1 = rng2.SpecialCells(xlBlanks)

                 Application.Iteration = True
                 rng1.FormulaR1C1 = "=AVERAGE(R[-1]C,R[1]C)"
                 Application.Iteration = False
                 rng2.Value = rng2.Value

    Next ws 
End Sub

我想知道这段代码缺少什么？

标签： excel

解决方案

您需要告诉它在哪个工作表 Range("L1:AB40") 上。

Set rng2 = ws.Range("L1:AB40")

如果没有空格，您还可以使用更好的错误控制。

Sub FillBlanks()

    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    For Each ws In Worksheets

         Set rng2 = ws.Range("L1:AB40")

         On Error Resume Next
         Set rng1 = rng2.SpecialCells(xlBlanks)
         on error goto 0

        if not rng1 is nothing then

             Application.Iteration = True
             rng1.FormulaR1C1 = "=AVERAGE(R[-1]C,R[1]C)"
             Application.Iteration = False
             rng2.Value = rng2.Value

        end if

    Next ws 

End Sub

推荐阅读