# Search and Move Text

#### mjwilyone

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### LilStevie

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

Code:
``````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

##### New Member
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

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

Code:
``````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

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

Thanks,
Mike

#### jindon

##### MrExcel MVP
Not sure...
Code:
``````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

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

Thanks,
Mike

#### LilStevie

##### Board Regular
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
Code:
``````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

##### MrExcel MVP
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

##### New Member
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

Replies
8
Views
147
Replies
0
Views
124
Replies
2
Views
535
Replies
3
Views
273
Replies
7
Views
201

Threads
1,190,916
Messages
5,983,569
Members
439,850
Latest member
suhailrocks786

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back