if & Loop
 

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

if & Loop
 

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)

"steven.holloway" wrote in
message ...
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

if & Loop
 

Have a look at what Autofilter does and also on the programming side of it:

http://www.contextures.com/xlautofilter03.html


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"steven.holloway" wrote:

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

if & Loop
 

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


"Bob Phillips" wrote:

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)

"steven.holloway" wrote in
message ...
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

if & Loop
 

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)

"steven.holloway" wrote in
message ...
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


"Bob Phillips" wrote:

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)

"steven.holloway" wrote in
message ...
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

if & Loop
 

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

"Bob Phillips" wrote:

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)

"steven.holloway" wrote in
message ...
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


"Bob Phillips" wrote:

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)

"steven.holloway" wrote in
message ...
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