Copy text from cell below based on condition Solved/Closed

Question

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

Configuration: Windows XP / Firefox 3.6.3

venkat1926 · Answer

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.

Copy text from cell below based on condition

1 response

Similar discussions

Subscribe To Our Newsletter!