Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect macro from stopping | Excel Discussion (Misc queries) | |||
Stopping a Looping Macro | Excel Programming | |||
stopping macro | Excel Programming | |||
Stopping an animation macro | Excel Programming | |||
My Macro keeps stopping?? | Excel Programming |