CODEWITHSUNDEEP
excelvba
Joseph
Joseph

Reputation: 607

Replace cells containing "#VALUE!" with average value of surrounding cells

I have columns of data with some cells containing #VALUE!. Annoying ofcourse, so I would like to write a macro which would replace these #VALUE! cells in each worksheet with the average value of 2 cells both above and below.

Eg.

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

I tried to follow this post and this post and have the following code but being a beginner, I am clearly missing something as I receive a "run-time error '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

Any help is greatly appreciated.

Upvotes: 3

Views: 442

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider:

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

and since Qty is a Range, don't use ActiveCell; instead:

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

EDIT#2:

Here is some working code:

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

Upvotes: 3

Related Questions