| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
continue to move data until all moved
Hi all,
ok, I have sheet of data in the following format: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc... etc...continues in the same format... what i need to do is move the status messages from column a to column b... so it should look like the following: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc....etc.... now I currently have the following code, it and it moves the first lot over, how do i get it to loop and get it to continue moving them over until they have all been moved? Sub move_status() Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12, 1)).Activate Selection.Cut Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12, 2)).Activate ActiveSheet.Paste End Sub any ideas?? many thanks, eadie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
One way:-
Sub MoveData() Dim r As Long Dim lrow As Long Application.ScreenUpdating = False lrow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count For r = lrow To 1 Step -1 With Cells(r, 1) If Left(.Value, 9) = "statusmsg" Then .Offset(, 1).Value = .Value .ClearContents End If End With Next r Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message m... Hi all, ok, I have sheet of data in the following format: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc... etc...continues in the same format... what i need to do is move the status messages from column a to column b... so it should look like the following: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc....etc.... now I currently have the following code, it and it moves the first lot over, how do i get it to loop and get it to continue moving them over until they have all been moved? Sub move_status() Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12, 1)).Activate Selection.Cut Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12, 2)).Activate ActiveSheet.Paste End Sub any ideas?? many thanks, eadie --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Hi, sorry Ken, I dont think that will work.....i havent tried the code
yet but correct me if im wrong...just by looking at your code, will that look for text with the first 9 characters equalling statusmsg and move it? if that is the case, then sorry my fault, i actually used statusmsg1,statusmsg2..etc..as an example only, they are actually random status messages (e.g. read-error01, active-dir, etc, etc), so i guess your code wont work? so again, sorry for being not so specific, any other ideas? thanks eadie. "Ken Wright" wrote in message ... One way:- Sub MoveData() Dim r As Long Dim lrow As Long Application.ScreenUpdating = False lrow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count For r = lrow To 1 Step -1 With Cells(r, 1) If Left(.Value, 9) = "statusmsg" Then .Offset(, 1).Value = .Value .ClearContents End If End With Next r Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message m... Hi all, ok, I have sheet of data in the following format: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc... etc...continues in the same format... what i need to do is move the status messages from column a to column b... so it should look like the following: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc....etc.... now I currently have the following code, it and it moves the first lot over, how do i get it to loop and get it to continue moving them over until they have all been moved? Sub move_status() Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12, 1)).Activate Selection.Cut Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12, 2)).Activate ActiveSheet.Paste End Sub any ideas?? many thanks, eadie --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Spot on, that was what it was doing. :-)
How do you determine what needs moving?? Is it simply every entry in Col A that doesn't have a corresponding entry in Col B needs moving over? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message om... Hi, sorry Ken, I dont think that will work.....i havent tried the code yet but correct me if im wrong...just by looking at your code, will that look for text with the first 9 characters equalling statusmsg and move it? if that is the case, then sorry my fault, i actually used statusmsg1,statusmsg2..etc..as an example only, they are actually random status messages (e.g. read-error01, active-dir, etc, etc), so i guess your code wont work? so again, sorry for being not so specific, any other ideas? thanks eadie. "Ken Wright" wrote in message ... One way:- Sub MoveData() Dim r As Long Dim lrow As Long Application.ScreenUpdating = False lrow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count For r = lrow To 1 Step -1 With Cells(r, 1) If Left(.Value, 9) = "statusmsg" Then .Offset(, 1).Value = .Value .ClearContents End If End With Next r Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message m... Hi all, ok, I have sheet of data in the following format: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc... etc...continues in the same format... what i need to do is move the status messages from column a to column b... so it should look like the following: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc....etc.... now I currently have the following code, it and it moves the first lot over, how do i get it to loop and get it to continue moving them over until they have all been moved? Sub move_status() Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12, 1)).Activate Selection.Cut Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12, 2)).Activate ActiveSheet.Paste End Sub any ideas?? many thanks, eadie --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Hi Ken,
Sorry for the delayed response, I have been particularly busy with another project I am working on. Coming back to this one, basically, I need moved, all the rows after the 'status:' row, up until, but not including the 'id1:' row again so for example this is how the data looks.... A B id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: read-error01 activated id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: initialising read-error55 access denied check connection execution completed successfully id1 thisisid1 id2 thisisid2 ....etc etc.... there are always four ID rows (id1:, id2:, id3:, id4:) one status row (status:) yet there may be any number of status messages after the 'status:' row and before the next lot of data. NOTE : I can tell you that there are always 5 blank rows after the last status message and before the next lot of data begins, regardless however many status messages there may be. so basically i need id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: read-error01 activated id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: initialising read-error55 access denied check connection execution completed successfully id1 thisisid1 id2 thisisid2 ....etc etc.... I dont actually mind if the blank rows are also moved over, that is not a problem. I hope that sheds some badly needed light on this problem, as im rack'ing my brains over this one! thanks for your help eadie. "Ken Wright" wrote in message ... Spot on, that was what it was doing. :-) How do you determine what needs moving?? Is it simply every entry in Col A that doesn't have a corresponding entry in Col B needs moving over? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message om... Hi, sorry Ken, I dont think that will work.....i havent tried the code yet but correct me if im wrong...just by looking at your code, will that look for text with the first 9 characters equalling statusmsg and move it? if that is the case, then sorry my fault, i actually used statusmsg1,statusmsg2..etc..as an example only, they are actually random status messages (e.g. read-error01, active-dir, etc, etc), so i guess your code wont work? so again, sorry for being not so specific, any other ideas? thanks eadie. "Ken Wright" wrote in message ... One way:- Sub MoveData() Dim r As Long Dim lrow As Long Application.ScreenUpdating = False lrow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count For r = lrow To 1 Step -1 With Cells(r, 1) If Left(.Value, 9) = "statusmsg" Then .Offset(, 1).Value = .Value .ClearContents End If End With Next r Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message m... Hi all, ok, I have sheet of data in the following format: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc... etc...continues in the same format... what i need to do is move the status messages from column a to column b... so it should look like the following: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc....etc.... now I currently have the following code, it and it moves the first lot over, how do i get it to loop and get it to continue moving them over until they have all been moved? Sub move_status() Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12, 1)).Activate Selection.Cut Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12, 2)).Activate ActiveSheet.Paste End Sub any ideas?? many thanks, eadie --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Try this:-
Sub runs down the column, and checks to see if the cell contains status: or is blank. If contains status it sets a flag to true and if blank it sets it to false, and then whilst the flag is set to true it will shift every cell to the right (Copying and deleting). Sub MoveData() Dim r As Long Dim md As Boolean Dim lrow As Long Application.ScreenUpdating = False lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row md = 0 For r = 2 To lrow With Cells(r, "A") If .Value = "status:" Then md = True ElseIf IsEmpty(.Value) Then md = False End If If md = True Then .Copy .Offset(0, 1) .ClearContents End If End With Next r Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message om... Hi Ken, Sorry for the delayed response, I have been particularly busy with another project I am working on. Coming back to this one, basically, I need moved, all the rows after the 'status:' row, up until, but not including the 'id1:' row again so for example this is how the data looks.... A B id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: read-error01 activated id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: initialising read-error55 access denied check connection execution completed successfully id1 thisisid1 id2 thisisid2 ...etc etc.... there are always four ID rows (id1:, id2:, id3:, id4:) one status row (status:) yet there may be any number of status messages after the 'status:' row and before the next lot of data. NOTE : I can tell you that there are always 5 blank rows after the last status message and before the next lot of data begins, regardless however many status messages there may be. so basically i need id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: read-error01 activated id1 thisisid1 id2 thisisid2 id3 thisisid3 id4 thisisid4 status: initialising read-error55 access denied check connection execution completed successfully id1 thisisid1 id2 thisisid2 ...etc etc.... I dont actually mind if the blank rows are also moved over, that is not a problem. I hope that sheds some badly needed light on this problem, as im rack'ing my brains over this one! thanks for your help eadie. "Ken Wright" wrote in message ... Spot on, that was what it was doing. :-) How do you determine what needs moving?? Is it simply every entry in Col A that doesn't have a corresponding entry in Col B needs moving over? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message om... Hi, sorry Ken, I dont think that will work.....i havent tried the code yet but correct me if im wrong...just by looking at your code, will that look for text with the first 9 characters equalling statusmsg and move it? if that is the case, then sorry my fault, i actually used statusmsg1,statusmsg2..etc..as an example only, they are actually random status messages (e.g. read-error01, active-dir, etc, etc), so i guess your code wont work? so again, sorry for being not so specific, any other ideas? thanks eadie. "Ken Wright" wrote in message ... One way:- Sub MoveData() Dim r As Long Dim lrow As Long Application.ScreenUpdating = False lrow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count For r = lrow To 1 Step -1 With Cells(r, 1) If Left(.Value, 9) = "statusmsg" Then .Offset(, 1).Value = .Value .ClearContents End If End With Next r Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "eadie" wrote in message m... Hi all, ok, I have sheet of data in the following format: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc... etc...continues in the same format... what i need to do is move the status messages from column a to column b... so it should look like the following: A B id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 statusmsg3 statusmsg4 statusmsg5 statusmsg6 statusmsg7 id1: thisisid1 id2: thisisid2 id3: thisisid3 id4: thisisid4 status: statusmsg1 statusmsg2 etc....etc.... now I currently have the following code, it and it moves the first lot over, how do i get it to loop and get it to continue moving them over until they have all been moved? Sub move_status() Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12, 1)).Activate Selection.Cut Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12, 2)).Activate ActiveSheet.Paste End Sub any ideas?? many thanks, eadie --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a cell even if its contents are moved moved/replaced | Excel Discussion (Misc queries) | |||
| can I write an Excel macro to pause for data entry then continue? | Excel Discussion (Misc queries) | |||
| Continue Formula if Data is Available | Excel Discussion (Misc queries) | |||
| Can you pause a macro in excel to input data and continue? | Excel Discussion (Misc queries) | |||
| Data Continue to next column? | Excel Discussion (Misc queries) | |||
Hybrid Mode
