straightlife said:
What am I doing wrong? I record a macro using a simple page up, and
then a tab, and it doesn't work at all. Nothing happens, plus, I once
closed the little "watch" pop-up toolbar window, and it never came
back. That little square? I'm on a mac powerbook running 10.4.5 and
Excel 2004 11.2. Please help, I'm doing repititious tasks and going
nuts.
The macro recorder doesn't record your actions so much as the results of
your actions. Nearly every recorded macro will be improved, or even made
usable, by editing. When I recorded a page-up followed by a tab, this is
what got recorded:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03/15/2006 by J.E. McGimpsey
'
'
ActiveWindow.LargeScroll Down:=-1
Range("H73").Select
End Sub
As you can see, it doesn't matter where I start when I rerun the macro,
I'll always end up at H73, which just happened to be where I landed
while recording. And LargeScroll, while it makes a selection in the GUI,
doesn't when run in a macro, so all that happens is the position of the
Active cell in the window is reset.
However, the relative reference control on the macro toolbar will change
the recorder to record in relative mode. The same recorded actions with
relative references selected is
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 03/15/2006 by J.E. McGimpsey
'
'
ActiveWindow.LargeScroll Down:=-1
ActiveCell.Offset(-56, 1).Range("A1").Select
End Sub
Which selects the cell 56 rows above and one column to the right of the
cell that's active. The number of rows recorded is dependent on the size
(height) of my window, as well as the row height(s). However, when I run
the macro, it will always move up 56 rows, regardless of the size of the
window when I run it. Note that if the active cell is in row 56 or less,
you'll get an error since row numbers can't be negative.
If I were to write the equivalent macro from scratch, I'd put in some
error handling:
Public Sub Up56Over1()
Dim nRow As Long
Dim nColumn As Long
nRow = Iif(ActiveCell.Row <= 56, 1, ActiveCell.Row - 56)
nColumn = Iif(ActiveCell.Column = Columns.Count, 0, 1)
ActiveSheet.Cells(nRow, nColumn).Select
End Sub
