Sheet navigation macro

John_Whin

Board Regular
Joined
Feb 26, 2013
Messages
78
Platform
  1. Windows
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
gf.jpg
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Something like

Code:
Sub Test()
    With Range("A65536").End(xlUp)
        .Offset(1, 0).Value = Val(Cstr(.Value)) + 1
        .Offset(1, 1).Select
    End With
End Sub
 
Upvote 0
Thanks, I've got similar navigation operations on other sheets, if I can't figure it out using your code I'll get back.
 
Upvote 0
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.
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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 ???
 
Upvote 0
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.

xc.jpg
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top