Stopping a Macro
 

Sorry, I don't understand. Range("A1").Select will take it back to A1, but I
think the tricky bit is working out if the macro has got to the bottom row.
The sheet is normally 10 - 20000 rows deep. It needs some code like"if this
row is the last row, go to A1 and stop"

Paul

"Bob Phillips" wrote:



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" wrote in message
...
Hi,
Ive got the macro below to run, the only problem is stopping it when it
gets
to the bottom of the sheet and ending in an orderly fashion. Ideally I
would
like it to go back to A1 when it has got to the bottom of the sheet.
If anyone has any suggestions, they would be very much appreciated.
Thanks for looking.
Paul

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 07/12/2005 by paul
'
' Keyboard Shortcut: Ctrl+z
'
Do
Cells.Find(What:="top 50", After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Selection.Copy
ActiveCell.Offset(0, -2).Select
Cells.Find(What:="Total of all issues", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Select
Loop
End Sub

Stopping a Macro
 

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 07/12/2005 by paul
'
' Keyboard Shortcut: Ctrl+z
'
Dim rw as Long
Range("A1").Select
Do
Cells.Find(What:="top 50", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
if rw = 0 then
rw = activecell.row
else
if activecell.row = rw then
Range("A1").Select
exit sub
end if
End if
ActiveCell.Offset(0, -1).Select
Selection.Copy
ActiveCell.Offset(0, -2).Select
Cells.Find(What:="Total of all issues", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Select
Loop
End Sub

--
Regards,
Tom Ogilvy

"Paul" wrote in message
...
Sorry, I don't understand. Range("A1").Select will take it back to A1, but
I
think the tricky bit is working out if the macro has got to the bottom
row.
The sheet is normally 10 - 20000 rows deep. It needs some code like"if
this
row is the last row, go to A1 and stop"

Paul

"Bob Phillips" wrote:



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" wrote in message
...
Hi,
Ive got the macro below to run, the only problem is stopping it when
it
gets
to the bottom of the sheet and ending in an orderly fashion. Ideally I
would
like it to go back to A1 when it has got to the bottom of the sheet.
If anyone has any suggestions, they would be very much appreciated.
Thanks for looking.
Paul

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 07/12/2005 by paul
'
' Keyboard Shortcut: Ctrl+z
'
Do
Cells.Find(What:="top 50", After:=ActiveCell, LookIn:=xlValues,
LookAt
_
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Selection.Copy
ActiveCell.Offset(0, -2).Select
Cells.Find(What:="Total of all issues", After:=ActiveCell,
LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Select
Loop
End Sub

Stopping a Macro
 

Mr. Paul,
I have also been in a tricky situation like this. I found a way out
to get the last row of the sheet, but under one condition. The sheet
should have a column of data in which every row has data, "Row number"
or "Sl. No." feilds, for example. You know the starting row of the
first data in this column (let it be A5). To find the last row, use
the code...

Range("A5").xldown.row

It is the same as pressing control and down arrow when you are at A5.
And it would be better to use the for each loop as given below

For each c in Range("A5:A" & Range("A5").xldown.row).cells

<Write your code

next



Understood?