Cut and Move relative to starting position

[Belair58]

Hello,

I'm attempting to find a word, highlight that cell and all the cells to the right of it. Cut that range and then paste it over 3 cells to the right.

Once I get the cut and paste working, I'll need to work on the loop to look at the entire column.

I think I'm close but I'm getting a run-time error 91 "Object Variable or With block variable not set" with the code below.

Any help would be appreciated.

Sub MoveTotals()
'

    Dim rngFound As Range
    Dim rngCapture As Range
    
    With Worksheets(1).Range("D:D")
    
        Set rngFound = .Find(What:="Totals:", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not (rngFound Is Nothing) Then
    
        rngCapture = rngFound.End(xlToRight).Select
        rngCapture.Cut
            rngFound.Offset(0, 3).Value = rngCapture
    
        Else
            MsgBox "The word 'Totals:' was not found in column D"
        End If
    End With
End Sub

 

[mumps]

Try:

Sub MoveTotals()
    Application.ScreenUpdating = False
    Dim rngFound As Range, sAddr As String, lCol As Long
    lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    With Worksheets(1).Range("D:D")
        Set rngFound = .Find(What:="Totals:", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not rngFound Is Nothing Then
            sAddr = rngFound.Address
            Do
                lCol = Cells(rngFound.Row, Columns.Count).End(xlToLeft).Column
                Range(Cells(rngFound.Row, 5), Cells(rngFound.Row, lCol)).Cut rngFound.Offset(0, 3)
                Set rngFound = .FindNext(rngFound)
            Loop While rngFound.Address <> sAddr
            sAddr = ""
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Belair58]

Mumps,

You rock!

I'm going to work through your changes to see where I went wrong.

Works great, and thanks!!

 

[mumps]

You are very welcome. Nice car! I had a 1966 Belair in my younger days.