if & Loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hope someone can help;

I am trying to write a macro to search column A in a worksheet for any
"Plan" entries. When it finds one I want it to cut the entire row the entry
is on and insert the row at row 500, then rename the "Plan" in cell A500 to
"New". I want this procedure to repeat until all "Plan" rows have been moved
and renamed.

By the way "Plan" entries are above row 500, so as each one is cut and
inserted at row 500, row 500 becomes 1 row less.

Also I want this to be sub operation within a current macro.

Can any one help?

Many thanks in advance
Steve
 
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "Plan" Then
.Rows(i).Cut
.Rows(501).Insert
.Range(TEST_COLUMN & "500").Value = "New"
End If
Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob,
I think this is close to what I need but it is not working fully (I think it
is stuck in a loop, may need a exit lop?).

Are you able to amend your formula so that the following limited data would
work;

Start Point
A1 = "Plan" B1 = "Star" C1 = 100
A2 = "Exist" B2 = "Cat" C2 = 50
A3 = "Plan" B3 = "Fish" C3 = 20

End Point
A1 = "Exist" A2 = "Cat" C3 = 50
A500 = "Plan" B500 = "Star" C500 = 100
A501 = "Plan" B501 = "Fish" C501 = 20

Many thanks again
Steve
 
Steve,

That works okay for me with that dataset already.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob it does work for the limited data, it just does not for my full
data, but I think I can work out what is wrong.

Many thanks again.
Steve
 
Back
Top