none
runtime error 91 RRS feed

  • Question

  • Hi,

    I do not know anything about macros. I am looking for a macro that clears a cell if its value is zero. I am trying to run the code below and I get a runtime error 91.

    This line ".FindNext(c).ClearContents" is highlighted.

    How do I fix this?

    Sub Step2ClearZeros()
    Dim c As Range
    With ActiveSheet.Cells
    Set c = .Find(What:="0", LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
    Do
    .FindNext(c).ClearContents
    Loop While Not c Is Nothing
    End If
    End With
    End Sub

    Tuesday, August 27, 2013 6:27 PM

Answers

  • Clearing the cell and finding the next cell with a 0 should be two separate steps:

    Sub Step2ClearZeros()
        Dim c As Range
        With ActiveSheet.Cells
            Set c = .Find(What:="0", LookIn:=xlValues, LookAt:=xlWhole)
            If Not c Is Nothing Then
                Do
                    c.ClearContents
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing
            End If
        End With
    End Sub

    But here is a much shorter version:

    Sub Step2ClearZeros()
        Cells.Replace What:=0, Replacement:="", LookAt:=xlWhole
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 27, 2013 7:16 PM