Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop | Excel Discussion (Misc queries) | |||
loop | Excel Discussion (Misc queries) | |||
Do Loop | Excel Worksheet Functions | |||
Next w/o For If/Then Loop | Excel Discussion (Misc queries) | |||
Please help with loop | Excel Discussion (Misc queries) |