Macor

Posted by Vic on September 30, 2001 5:11 PM

I am a new user of Excel 97 (windows 98) and am trying (with no success) to create a macro to move across a worksheet and then down one row to the beginning point for another entry. I prefer to hit the Enter key to make all cell movements.

Example:

Columns
A B C D E
Name Check # Amount

1 Vic 1 100
2 Joe 2 200
3 Pete 4 150

I would like to go from A1 press enter and goto C1 then press enter and goto E1 then press enter and go down and back to A2.

All entries will occur within a specified range.

The amount blank columns will vary between different worksheets. The worksheet could have 2 blank columns, only one or none. I realize I could hide the blank columns but would rather not.

Also, I was wondering if a macro could be created that, after entering information in the required cells of the first row, would allow the cursor to move to the same cells that ahve the entries made in them as listed on the first row.

Also, please advise as to how to install the macro as my workbook is a Template.

I realize the above reqest is rather substantial but would sincerely appreciate anyones help in trying to solve my problem.

Thanks and "lets Roll"

Posted by Damon Ostrander on October 01, 2001 9:04 AM

Hi Vic,

Here is a little macro that returns the selected cell one row down and column 1 when you go past column 5. If you have not already done so, you will also need to set Tools -> Options -> Edit tab -> Move selection after enter checkbox, check it and set direction to Right.

This macro must be placed into the worksheet's event code pane. To do this, just right-click on the worksheet's tab, select View Code, and paste the code into the VBE code pane that appears. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column > 5 Then
Cells(Target.Row + 1, 1).Select
End If
End Sub

If you want it to also skip columns that have no headings, use the following code instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row > 1 Then
If Target.Column > 5 Then
Cells(Target.Row + 1, 1).Select
ElseIf Cells(1, Target.Column) = "" Then
Cells(Target.Row, Target.Column + 1).Select
End If
End If
End Sub

Happy computing.

Damon