Search and Move Text

[mjwilyone]

I am working on a macro and, as part of it, I would like to look at column H of my spreadshet. If the wording in any cell in column H begins with the word, "total" I would like the cell contents of the cell in column G and up one to be dropped down one. Here is an example:

Let's say that the words "total money" is found in cell H2. I would like the cell contents of cell G1 to be moved to G2, making boths cell side-by-side having text in them - G2 and H2.

Can anyone help me with this?

Thanks so much,
Mike

 

[LilStevie]

Give this a try. (make sure you backup your workbook first)

Sub Findit()

    Dim LastRow As Long
    Dim x As Long
    
    LastRow = Worksheets("sheet1").Range("g65536").End(xlUp).Row
    
    For x = LastRow To 1 Step -1
    
        If Left(Cells(x, 7), 5) = "total" Then  ' find cell that matched criteria
            Cells(x, 7).Offset(0, 1).Value = Cells(x, 7).Offset(-1, 1).Value ' copy H cell down
            Cells(x, 7).Offset(-1, 1).Value = ""  'clear moved cell data.
        End If
    Next x
        
End Sub

 

[mjwilyone]

LilStevie,

The code does not appear to do anything. Does it look for cells in column H having text that begins with the word "Total", then move the cell to the left and up one down one?

Thanks,
Mike

 

[LilStevie]

Sorry about that, I read your post incorrectly. Try this:

Sub Findit()

    Dim LastRow As Long
    Dim x As Long
    
    LastRow = Worksheets("sheet1").Range("h65536").End(xlUp).Row
    
    For x = LastRow To 1 Step -1
    
        If Left(Cells(x, 8), 5) = "total" Then  ' find h row cell that matches criteria
            Cells(x, 8).Offset(0,-1).Value = Cells(x, 8).Offset(-1, -1).Value ' copy g row cell down
            Cells(x, 8).Offset(-1,-1).Value = ""  'clear moved cell data.
        End If
    Next x
        
End Sub

 

[mjwilyone]

I still get no reponse of any kind. There is no error ... just no progress either. Any ideas?

Thanks,
Mike

 

[jindon]

Not sure...

Sub test()
Dim r As Range
For Each r In Range("g2", Range("g" & Rows.Count).End(xlUp))
    If LCase(r.Value) Like "total*" Then r.Offset(,1).Value = r.Offset(-1,1).Value
Next
End Sub

 

[mjwilyone]

No ... still does not do the trick. Any more help?

Thanks,
Mike

 

[LilStevie]

Mike,
the code works fine for me. Can you confirm that your "total" is in the H column and the data that you want to move down is in the G column? Your pasting this code into a standard module and then running it..

try this and let me know what the message box tells you

Sub Findit()

    Dim LastRow As Long
    Dim x As Long
    Dim i As Integer
    
    
    LastRow = Worksheets("sheet1").Range("h65536").End(xlUp).Row
    i = 0
    For x = LastRow To 2 Step -1
    
        If Left(Cells(x, 8), 5) = "total" Then  ' find h row cell that matches criteria
            Cells(x, 8).Offset(0, -1).Value = Cells(x, 8).Offset(-1, -1).Value ' copy g row cell down
            Cells(x, 8).Offset(-1, -1).Value = "" 'clear moved cell data.
            i = i + 1
        End If
    Next x
        MsgBox "Corrected " & i & " 'total' matches"
End Sub

 

[jindon]

No ... still does not do the trick. Any more help?

Thanks,
Mike

It doesn't help to understand your problem.

How does it not working as you want ?

 

[mjwilyone]

LilStevie,

Your code was correct ... it did work, once I noticed that my spreadsheet used the word "Total" not "total" as I had originally placed in my request for help. I am sorry to put you through all that work. Didn't think about case sensitivity.

Thank you for code that works!

Mike