excel - 将包含 "#VALUE!"的单元格替换为周围单元格的平均值

Question

我有一些数据列，其中一些单元格包含 #VALUE!。当然很烦人，所以我想编写一个宏，将每个工作表中的这些 #VALUE! 单元格替换为上面和下面 2 个单元格的平均值。

例如。

    A               A
1   1               1
2   2               2
3  #VALUE!    ->    3
4   4               4
5   5               5

我试图关注this post和 this post并具有以下代码，但作为初学者，当我收到“运行时错误'13'”时，我显然错过了一些东西。

Sub Test()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets

    With wks
        Dim Qty As Range

        For Each Qty In Range("A:ZZ").Cells

            If InStr(1, (Qty.Value), "#VALUE!") Then
            ActiveCell.FormulaR1C1 = "=AVERAGE(R[-2]C:R[-1]C,R[1]C:R[2]C)"
            Range("A:ZZ").Select

            End If
        Next
    End With
Next
End Sub

非常感谢任何帮助。

Answer 1

考虑:

If InStr(1, (Qty.Text), "#VALUE!") Then

由于数量是一个范围，因此不要使用ActiveCell;来代替:

Qty.FormulaR1C1 = "=AVERAGE(R[-2]C:R[-1]C,R[1]C:R[2]C)"

编辑#2:

这是一些工作代码:

Sub Test()
    Dim wks As Worksheet, Qty As Range

    For Each wks In ThisWorkbook.Worksheets
        With wks
            For Each Qty In Intersect(.UsedRange, .Range("A:ZZ").Cells)
                If InStr(1, (Qty.Text), "#VALUE!") Then
                    Qty.FormulaR1C1 = "=AVERAGE(R[-2]C:R[-1]C,R[1]C:R[2]C)"
                End If
            Next
        End With
    Next wks
End Sub