continue to move data until all moved  
Author Message
DST





PostPosted: Thu Jan 08 13:15:06 CST 2004 Top

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

Excel461  
 
 
Ken





PostPosted: Thu Jan 08 13:15:06 CST 2004 Top

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





> 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


 
 
deathrowzfinest2k





PostPosted: Fri Jan 09 08:45:52 CST 2004 Top

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.


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


> > 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
 
 
Ken





PostPosted: Fri Jan 09 17:01:45 CST 2004 Top

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





> 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.
>


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


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


 
 
deathrowzfinest2k





PostPosted: Wed Jan 14 11:37:36 CST 2004 Top

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.




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


> > 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.
> >


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


> > > > 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
 
 
Ken





PostPosted: Wed Jan 14 14:03:27 CST 2004 Top

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





> 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.
>
>
>


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


> > > 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.
> > >


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


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


 
 
deathrowzfinest2k





PostPosted: Thu Jan 15 12:13:19 CST 2004 Top

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









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


> > 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.
> >
> >
> >


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


> > > > 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.
> > > >


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


> > > > > > 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
 
 
Ken





PostPosted: Thu Jan 15 15:23:36 CST 2004 Top

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





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


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


> > > 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.
> > >
> > >
> > >


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


> > > > > 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.
> > > > >


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


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


 
 
Ken





PostPosted: Thu Jan 15 15:39:09 CST 2004 Top

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


 
 
deathrowzfinest2k





PostPosted: Fri Jan 16 10:49:47 CST 2004 Top

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








> 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
 
 
Ken





PostPosted: Fri Jan 16 11:38:18 CST 2004 Top

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





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


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


 
 
Ken





PostPosted: Fri Jan 16 12:03:52 CST 2004 Top

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