If Then formula for including data in preceding row to data currently there

BillJ

New Member
Joined
Dec 5, 2013
Messages
14
I have a spreadsheet that currently contains numbers and is separated by "XXXXX". I would like to add the number in the previous row to the X's and do the same operation for the whole column. Any thoughts? Here is a sample of my current data:

A
1
B
1188
C

11189
11190
XXXXXXXXXXXXXXX
21191**
21192
21193
21194

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody></tbody>

In column B I need the XXXXX to be replaced with 1190XXXXX. Haven't be able to find any help with this and have been searching for about an hour here.

Thanks, any help would be greatly appreciated.

Bill
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Bill. Try:
Code:
Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("B2:B" & LastRow)
        If rng = "XXXXX" Then
            rng = rng.Offset(-1, 0) & rng
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,217,456
Messages
6,136,754
Members
450,025
Latest member
Beginner52

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

Which adblocker are you using?

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
Back
Top