Loops
 

Thanks to the people who answered my last post. Problem 2. I have
written a loop which works but I want it to stop when the activecell
has the value "fuggle". It stops for a few seconds at cell fuggle then
goes round again. The code is below any ideas?

Sub Customer()
'
' Customer Macro
' Macro recorded 07/07/2005 by James Fuggle
'

'
Sheets.Add
ActiveSheet.Name = "Customers"
Sheets("Duration and Consumables Bookin").Select

Do While ActiveCell < "fuggle"
Cells.Find(What:="customer", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Customers").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Duration and Consumables Bookin").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub


--
fugfug
------------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...o&userid=24950
View this thread: http://www.excelforum.com/showthread...hreadid=385196

Loops
 

Have a look in vba HELP index for find and then findnext. There is a good
example. While you're at it, eliminate the selections.

--
Don Guillett
SalesAid Software

"fugfug" wrote in
message ...

Thanks to the people who answered my last post. Problem 2. I have
written a loop which works but I want it to stop when the activecell
has the value "fuggle". It stops for a few seconds at cell fuggle then
goes round again. The code is below any ideas?

Sub Customer()
'
' Customer Macro
' Macro recorded 07/07/2005 by James Fuggle
'

'
Sheets.Add
ActiveSheet.Name = "Customers"
Sheets("Duration and Consumables Bookin").Select

Do While ActiveCell < "fuggle"
Cells.Find(What:="customer", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Customers").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Duration and Consumables Bookin").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub


--
fugfug
------------------------------------------------------------------------
fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
View this thread: http://www.excelforum.com/showthread...hreadid=385196

Loops
 

Sub Customer()
'
' Customer Macro
' Macro recorded 07/07/2005 by James Fuggle
'

'
Sheets.Add
ActiveSheet.Name = "Customers"
Sheets("Duration and Consumables Bookin").Select
Range("A1").Select
Cells.Find(What:="fruggle", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
rw = ActiveCell.Row
Range("A1").Select
Do While ActiveCell.Row < rw
Cells.Find(What:="customer", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If ActiveCell.Row = rw Then Exit Do
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Customers").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Duration and Consumables Bookin").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

--
Regards,
Tom Ogilvy

"fugfug" wrote in
message ...

Thanks to the people who answered my last post. Problem 2. I have
written a loop which works but I want it to stop when the activecell
has the value "fuggle". It stops for a few seconds at cell fuggle then
goes round again. The code is below any ideas?

Sub Customer()
'
' Customer Macro
' Macro recorded 07/07/2005 by James Fuggle
'

'
Sheets.Add
ActiveSheet.Name = "Customers"
Sheets("Duration and Consumables Bookin").Select

Do While ActiveCell < "fuggle"
Cells.Find(What:="customer", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Customers").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Duration and Consumables Bookin").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub


--
fugfug
------------------------------------------------------------------------
fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
View this thread: http://www.excelforum.com/showthread...hreadid=385196

Loops
 

Thanks alot, works now

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38519