Sheet navigation macro

[John_Whin]

Hockey spreadsheet, in column A I've got the game number and enter scoring info beginning column B. What I want to do is set up macro that navigates to first open cell and the takes the number in the last non empty cell, adds 1 then moves cursor right to a specific column.

So on this sheet I've assigned a macro to A1. I want it to move cursor down column A to the last non empty cell (A83) take that value and add one to it and put that result in A84 then move cursor to B84 for input. I'll copy/paste data into the next row(s). The next game I want it to do same thing.

Thanks

 

[mikerickson]

Something like

Sub Test()
    With Range("A65536").End(xlUp)
        .Offset(1, 0).Value = Val(Cstr(.Value)) + 1
        .Offset(1, 1).Select
    End With
End Sub

 

[John_Whin]

Thanks, I've got similar navigation operations on other sheets, if I can't figure it out using your code I'll get back.

 

[John_Whin]

actually if I want to do same thing on another sheet but instead of last step moving cursor right 1 space I want to move it 3 spaces what is the code?

and another sheet just move down column to next empty cell.

 

[John_Whin]

figured it out, thanks

 

[John_Whin]

Actually figured out how to move cursor further to the right but can't figure out the simple part for another sheet

for data in column I how do I go to first empty cell in that column.

 

[mikerickson]

First empty cell would be

Range("I:I").SpecialCells(xlCellTypeBlanks).Cells(1,1)

 

[John_Whin]

Tried to insert it

 

[mikerickson]

The code I posted is the sytnax that will return the cell.
What you want to do with it is determined by if you want to .Select it, or set its .Value or assing a variable to it or ???

 

[John_Whin]

Thanks for your time and patience, this is frustrating to me because back in the day my minor was IS (had all A's and 1 B), learned to code in Pascal, PLI, Cobol, some assembler .... but that dates me.

Though I can do a simple Ctrl down and get to the last non blank cell in the column I would like to navigate with a macro that is enabled by clicking on a shape (button) in cell I1.

My goal is to use the macro and have cursor move to the first blank cell in column I on this sheet. So as the sheet is I would want to click on the button and go to I 17. Next game I want to go to I 18 etc.