VBA Loop Through Cells

Question

I just started coding in VBA and I am trying to write code that goes through my sheet in Excel and, for each cell that has a value "-P", it deletes it. Once it is done, it generates a box message that all the "-P were deleted" and that there were not any p's. It generates a message, "-P was not Found".

The program only runs until it finds one cell and goes through the if statement and then it is done. I would have to run it multiple times in order to delete all the p's in my document. I have tried for loops and do until loops … but I am getting more confused.

Sub RemoveP()

    Dim ws As Worksheet
    Dim xcell As Object
    Dim rng As Range

    Set ws = Worksheets(1)
    Set rng = ws.Range("B:B")
    
    For Each xcell In rng
        If xcell Is Nothing Then
            MsgBox "-P was not Found"
        Else
            xcell.Replace What:="-P", Replacement:="", lookAt:=xlPart, SearchOrder:=xlByColumns, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                  
                MsgBox "-P was Deleted"
        End If
            
    Next xcell
      
End Sub

I would appreciate some help.

Within your loop, xcell will never be Nothing. Is "-P" only part of the cell value? — Tim Williams
– Tim Williams, Commented Oct 3, 2022 at 20:49

Tim Williams · Accepted Answer · 2022-10-03 21:00:17Z

1

You don't need to do this in a loop:

Sub RemoveP()

    Dim ws As Worksheet
    Dim rng As Range

    Set ws = Worksheets(1)
    'only work on the occupied part of the column
    Set rng = ws.Range("B1:B" & ws.Cells(Rows.Count, "B").End(xlUp).Row)
    
    If Not IsError(Application.Match("*-p*", rng, 0)) Then 'any -P in rng?
        rng.Replace What:="-P", Replacement:="", lookAt:=xlPart, _
                   SearchOrder:=xlByColumns, MatchCase:=False, _
                   SearchFormat:=False, ReplaceFormat:=False
        MsgBox "-P was Deleted"
    Else
        MsgBox "-P was not Found"
    End If
    
End Sub

answered Oct 3, 2022 at 21:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Loop Through Cells

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related