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

[BillJ]

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
1	1189
1	1190
XXXXX	XXXXX	XXXXX
2	1191	**
2	1192
2	1193
2	1194

<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

 

[mumps]

Hi Bill. Try:

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]

Wow, that's all I've got!!!!!!!!!!!!! And Thank You of course!!!!!!!!!!!!

Bill J

 

[mumps]

My pleasure.