Help with 'If Not c is Nothing' loop

[Nooblet]

Hi all,

I have a macro that works fine, except it performs the steps one time too many.

As far as I can figure it, once it realises that If Not c is Nothing is no longer true (i.e. c is Nothing), it still performs all of the steps afterwards one more time before ending the loop.

Can anyone help with adjusting the code (below) so that it stops doing this?

Sub Macro1()
'
' Macro1 Macro
'

Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
    Do
        Set c = SrchRng.Find("Bob:", LookIn:=xlValues)
        If Not c Is Nothing Then c.Select
        Selection.Cut
        ActiveCell.Offset(-1, 1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Loop While Not c Is Nothing
        
'
End Sub

Thanks for your assistance

 

[Marcelo Branco]

Hi,

Maybe this

Sub Macro1()
'
' Macro1 Macro
'
    Dim srchRng As Range, c As Range
    Set srchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
    Set c = srchRng.Find("Bob:", LookIn:=xlValues)
    Do While Not c Is Nothing
        c.Select
        Selection.Cut
        ActiveCell.Offset(-1, 1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Set c = srchRng.Find("Bob:", LookIn:=xlValues)
    Loop
End Sub

HTH

M.

 

[Marcelo Branco]

Improved

Sub Macro2()
'
' Macro2 Macro
'
    Dim srchRng As Range, c As Range
    Set srchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
    Set c = srchRng.Find("Bob:", LookIn:=xlValues)
    Do While Not c Is Nothing
        c.Cut c.Offset(-1, 1)
        Set c = srchRng.Find("Bob:", LookIn:=xlValues)
    Loop
    
    Application.CutCopyMode = False
End Sub

M.

 

[Nooblet]

Thanks, looks like it is working fine now

 

[Marcelo Branco]

Thanks, looks like it is working fine now

You are welcome and tks for the feedback

M.