Excel 2007 - Macro (VB) code for arrow down & arrow up
 

Using Excel's "Record" macro ignores arrow movements. Could someone please
give me the macro code for down one cell and up one cell.
Thanks

Excel 2007 - Macro (VB) code for arrow down & arrow up
 

Hi Grindy,

To go up one cell...

Sub OneUp()
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End Sub

Going down use...

ActiveCell.Offset(1, 0).Select

Ed Ferrero
www.edferrero.com

Excel 2007 - Macro (VB) code for arrow down & arrow up
 

Thanks for the quick response Ed. A related question...
What is the purpose of the "If/Then" part of your code, ie.

If ActiveCell.Row 1 Then

Just trying to learn why it is needed.

Thanks again,
bob

"Ed Ferrero" wrote:

Hi Grindy,

To go up one cell...

Sub OneUp()
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End Sub

Going down use...

ActiveCell.Offset(1, 0).Select

Ed Ferrero
www.edferrero.com

Excel 2007 - Macro (VB) code for arrow down & arrow up
 

Hi Grindy,

Thanks for the quick response Ed. A related question...
What is the purpose of the "If/Then" part of your code, ie.

If ActiveCell.Row 1 Then

Just trying to learn why it is needed.

If the active cell is in row 1, going up one cell will go to row zero, which
does not exist - you will get an error.

Ed Ferrero
www.edferrero.com

Excel 2007 - Macro (VB) code for arrow down & arrow up
 

If the ActiveCell is on Row 1, you can't go up (the Offset function would
generate an error if you tried)... the If test makes sure the code doesn't
try. By the way, it might not be clear from Ed's posting, but you don't need
that test for the OneDown macro, but you should test to make sure you aren't
at the bottom of the worksheet...

Sub OneDown()
If ActiveCell.Row < ActiveSheet.Rows.Count Then
ActiveCell.Offset(1, 0).Select
End If
End Sub

--
Rick (MVP - Excel)


"Grindy" wrote in message
...
Thanks for the quick response Ed. A related question...
What is the purpose of the "If/Then" part of your code, ie.

If ActiveCell.Row 1 Then

Just trying to learn why it is needed.

Thanks again,
bob

"Ed Ferrero" wrote:

Hi Grindy,

To go up one cell...

Sub OneUp()
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End Sub

Going down use...

ActiveCell.Offset(1, 0).Select

Ed Ferrero
www.edferrero.com

Excel 2007 - Macro (VB) code for arrow down & arrow up
 

Wow, you 2 are great!
I completly understand now, and really appreciate both of you taking the
time to explain this stuff to a VB newbee...
I totally get it.... :)
bob

"Rick Rothstein" wrote:

If the ActiveCell is on Row 1, you can't go up (the Offset function would
generate an error if you tried)... the If test makes sure the code doesn't
try. By the way, it might not be clear from Ed's posting, but you don't need
that test for the OneDown macro, but you should test to make sure you aren't
at the bottom of the worksheet...

Sub OneDown()
If ActiveCell.Row < ActiveSheet.Rows.Count Then
ActiveCell.Offset(1, 0).Select
End If
End Sub

--
Rick (MVP - Excel)


"Grindy" wrote in message
...
Thanks for the quick response Ed. A related question...
What is the purpose of the "If/Then" part of your code, ie.

If ActiveCell.Row 1 Then

Just trying to learn why it is needed.

Thanks again,
bob

"Ed Ferrero" wrote:

Hi Grindy,

To go up one cell...

Sub OneUp()
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End Sub

Going down use...

ActiveCell.Offset(1, 0).Select

Ed Ferrero
www.edferrero.com