continue to move data until all moved

eadie · Jan 8, 2004

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

Ken Wright · Jan 8, 2004

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

eadie · Jan 9, 2004

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 · Jan 9, 2004

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?

eadie · Jan 14, 2004

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 · Jan 14, 2004

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 said:
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" <[email protected]> wrote in message

eadie · Jan 15, 2004

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 · Jan 15, 2004

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 said:
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" <[email protected]> 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
----------------------------------------------------------------------------

Col A
that

Click to expand...

--------------------------------------------------------------------------------------------------------------------------------------------------------

Ken Wright · Jan 15, 2004

Oops - Should have snipped that :-(

eadie · Jan 16, 2004

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 · Jan 16, 2004

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 :-)

Ken Wright · Jan 16, 2004

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