Copy text from cell below based on condition

Solved/Closed
Report
-
 Paul -
Hi,

I'm looking for a macro that will copy the text from the cell below to replace the cell above where the cell above meets a certain criteria.

i.e.

A1 = ***
A2 = text
A3 = "blank"
A4 = ***
A5 = text 2

I'd like to run the macro to replace the contents of A1 with the contents of A2 (and A4 with A5 contents) only where the criteria set = ***. Giving the following result:

A1 = text
A2 = text
A3 = "blank"
A4 = text 2
A5 = text 2

Is this possible?

Thanks in advance,
Paul

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
your sample data is like this from A1 down

***
text

***
text2


try this macro

Sub test()
Dim r As Range, c As Range
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0))
For Each c In r
If c = "***" Then
c = c.Offset(1, 0)
End If
Next c
End Sub


As you are messing up with the data please save the original data safely somewhere.
0
Excellent, that's exactly what I needed.

Thanks very much,

Paul
0