| 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Top man!!! Thanks Ken, it worked a treat...
there is one other thing i'd like to do with this, to make data more presentable.....i'd like to create a new worksheet, possibly called 'output', and then simply to copy and then paste each lot of data ('lot' meaning each set of status messages) on the new worksheet horizontally, with each lot occupying one row possibly.... so the new worksheet created would like something like this: (the old worksheet need not be deleted) A B C D E read-error01 activated initialising read-error55 access denied check connection exce.. etc...etc... i havent had much time to look at it, but im thinking i could use the same sort of principle as your previous solution, where the code could possibly run down the second column, check if the cell contains any data? (not null?) or if its blank...if it contains data then to copy it, and paste special (transpose?) on the first available row on the new worksheet. the bit where im getting confused is to only do a lot of data at time and then to do the two checks, one to check if it contains data on primary worksheet then second to check whether the row is empty or not on the second 'output' worksheet?? any ideas if this can be done? cheers, eadie "Ken Wright" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Sub will delete any sheet called Output that already exists, and then run the
normal routine, populating sheet Output with all data that it moves over from Col A to Col B in first sheet. Sub MoveData1() Dim r As Long Dim md As Boolean Dim lrow As Long Dim otpr As Integer Dim otpc As Integer Dim wks1 As Worksheet Dim wks2 As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next Sheets("Output").Delete On Error GoTo 0 Application.DisplayAlerts = True Set wks1 = ActiveSheet Set wks2 = Worksheets.Add wks2.Name = "Output" lrow = wks1.Cells(Rows.Count, "A").End(xlUp).Row otpr = 0 otpc = 1 md = 0 For r = 2 To lrow With wks1.Cells(r, "A") If .Value = "status:" Then md = True otpr = otpr + 1 otpc = 1 ElseIf IsEmpty(.Value) Then md = False End If If md = True Then .Copy wks2.Cells(otpr, otpc) .Copy .Offset(0, 1) .ClearContents otpc = otpc + 1 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... Top man!!! Thanks Ken, it worked a treat... there is one other thing i'd like to do with this, to make data more presentable.....i'd like to create a new worksheet, possibly called 'output', and then simply to copy and then paste each lot of data ('lot' meaning each set of status messages) on the new worksheet horizontally, with each lot occupying one row possibly.... so the new worksheet created would like something like this: (the old worksheet need not be deleted) A B C D E read-error01 activated initialising read-error55 access denied check connection exce.. etc...etc... i havent had much time to look at it, but im thinking i could use the same sort of principle as your previous solution, where the code could possibly run down the second column, check if the cell contains any data? (not null?) or if its blank...if it contains data then to copy it, and paste special (transpose?) on the first available row on the new worksheet. the bit where im getting confused is to only do a lot of data at time and then to do the two checks, one to check if it contains data on primary worksheet then second to check whether the row is empty or not on the second 'output' worksheet?? any ideas if this can be done? cheers, eadie "Ken Wright" wrote in message ... 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 --- 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Oops - Should have snipped that :-(
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- --- 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Hi again Ken, thanks for that , it works fine. Sorry ive just
realised that i'd be better off doing it slightly differently. After moving the status messages to column B, i'd then like to copy all of column B (not just the status messsages) and paste the data again horizontally in the 'output' worksheet. I tried using another WITH statement referring to column B , and then tried selecting and copying the data over.... Columns("B:B").Select ..Copy wks2.Cells(otpr, otpc) but it didnt seem to work. this is what im trying to achieve... A B... ...E F G thisisid1 thisisid2 status: read-error01 activated thisisid1 thisisid2 status: executing accessdenied etc..etc.. so basically, everything in column B, BUT as like before, a set of data per row. Sorry for being such a pain...I am definetly not going to change my mind after this one!!! cheers, eadie "Ken Wright" wrote in message ... Oops - Should have snipped that :-( -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- --- 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Just realised that the code is now moving the cell with 'Status:' in it over as
well. Is that OK or not OK? Will take a look at the other bit shortly :-) -- 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 again Ken, thanks for that , it works fine. Sorry ive just realised that i'd be better off doing it slightly differently. After moving the status messages to column B, i'd then like to copy all of column B (not just the status messsages) and paste the data again horizontally in the 'output' worksheet. I tried using another WITH statement referring to column B , and then tried selecting and copying the data over.... Columns("B:B").Select .Copy wks2.Cells(otpr, otpc) but it didnt seem to work. this is what im trying to achieve... A B... ...E F G thisisid1 thisisid2 status: read-error01 activated thisisid1 thisisid2 status: executing accessdenied etc..etc.. so basically, everything in column B, BUT as like before, a set of data per row. Sorry for being such a pain...I am definetly not going to change my mind after this one!!! cheers, eadie "Ken Wright" wrote in message ... Oops - Should have snipped that :-( -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- --- 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
continue to move data until all moved
Hopefully you didn't want the Status bit to move, so I fixed that, and the rest
works the way you wanted now as well. Sub MoveData1() Dim md As Boolean Dim r As Long Dim lrow As Long Dim lrow As Long Dim otpr As Integer Dim otpc As Integer Dim wks1 As Worksheet Dim wks2 As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next Sheets("Output").Delete On Error GoTo 0 Application.DisplayAlerts = True Set wks1 = ActiveSheet Set wks2 = Worksheets.Add wks2.Name = "Output" lrow = wks1.Cells(Rows.Count, "A").End(xlUp).Row otpr = 0 otpc = 1 md = 0 For r = 2 To lrow With wks1.Cells(r, "A") If .Offset(-1, 0).Value = "status:" Then otpr = otpr + 1 'copies each of the 4 id messages to the other sheet For n = 5 To 2 Step -1 .Offset(-n, 0).Copy wks2.Cells(otpr, 6 - n) Next n 'sets the 'move data' flag to TRUE md = True otpr = otpr + 1 otpc = 1 ElseIf IsEmpty(.Value) Then md = False End If 'If the 'move data' flag is set to TRUE then it copies the data across to sheet 2 'then copies it across one column, then deletes it from original cell, and moves on. If md = True Then .Copy wks2.Cells(otpr, otpc) .Copy .Offset(0, 1) .ClearContents otpc = otpc + 1 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 :-) ---------------------------------------------------------------------------- <snip --- 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) | |||
Linear Mode
