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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,592
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
 

BillJ

New Member
Joined
Dec 5, 2013
Messages
14
Wow, that's all I've got!!!!!!!!!!!!! And Thank You of course!!!!!!!!!!!!

Bill J
 

Forum statistics

Threads
1,137,063
Messages
5,679,401
Members
419,825
Latest member
MegastarMagus

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
Top