Command Key for GOTO command and relative reference

[riwiseuse]

Trying to somewhat automate a laborious process of changing and copying down labels every 607 rows in two columns.

So, I split the screen and advance the bottom screen to the bottom of the data I'm trying to change and the top screen at the top.

I am using the GOTO command and two things could be easier.

First problem is that it would be way easier if their were a command key. Using the mouse to do this from this menus is a pain in the butt. In the good 'ole days - say 2.0 a lot of this stuff had key combos to address.

Is there some preference area where I can assign a command key to a certain menu function?

Second issues is that the GOTO command dialogue box doesn't seem to accept relative references, only the A1 format. So I need to move 607 lines each time. It would be way easier if I could just tell it to offset 607. Otherwise I have to look at what line I'm on and add 607 in my head to that line. Obviously I can do it quickly or add 610 which is easier and still get basically where I need to go, but I feel like this would be a perfect application for relative reference and that is what I would use if I were writing a macro.

I haven't totally automated the process because the change I have to make is different for each stop and not readily programmed.

Any suggestions always appreciated.

Brian

[JBeaucaire]

Create your own hotkey shortcut macro.

1) Press Alt-F8 to open the macro windown
2) Type in the name for your new macro
3) Click on CREATE
4) Paste in this code to the window that appears between the sub lines:

Please Login or Register  to view this content.

5) Press Alt-F11 to close the VBEditor
6) Press Alt-F8 to open the macro window again
7) Highlight the new macro name
8) Select the Options button
9) Enter a shortcut key... for instance "g" to replace the builtin Ctrl-G shortcut


Now whenever you press CTRL-G (or whatever you chose) your own macro will take over and prompt you to jump wherever, just like you wanted.

[riwiseuse]

Create your own hotkey shortcut macro.

1) Press Alt-F8 to open the macro windown
2) Type in the name for your new macro
3) Click on CREATE
4) Paste in this code to the window that appears between the sub lines:

Please Login or Register  to view this content.

5) Press Alt-F11 to close the VBEditor
6) Press Alt-F8 to open the macro window again
7) Highlight the new macro name
8) Select the Options button
9) Enter a shortcut key... for instance "g" to replace the builtin Ctrl-G shortcut


Now whenever you press CTRL-G (or whatever you chose) your own macro will take over and prompt you to jump wherever, just like you wanted.

J

thanks for your input. I'm getting somewhere but not there. It is time for me to conquer this VBEditor. I haven't been able to write macros since they stopped the process I learned which was to write in cells on a macro sheet back in Excel 2.0. It's not that that was so much more sensible than the VBE approach. The whole naming, defining convention seemed pretty arbitrary and you just had to learn it, it wasn't intuitive. But I had learned it, and I just can't quite figure how to translate what I knew how to do writing in cells on a macro sheet into the VBE world.

I'm sure part of the problem in following your thoughtfully complete instructions is that I'm running 2004 on a Mac. None of the Alt Function codes work.

I can go to the tools menu, choose the Macros command and type in a name for a macro and click create.

That gets me a window with sub lines. I assume that stands for something like subroutine and the code belongs between them. I'm shocked that the sub end doesn't take double parentheses. This is where i would have used to type RETURN() in a cell in the good ole days.

So I pasted the code, including the header
"Dim Rws as Long"

Is that line doing anything or an internal name or note?

When I first ran the macro it jumped out at "move how many rows?" and said something like 'improper command line'

Now when I run it it I get 'can't execute code in break mode'. No idea what break mode is or how I got into it, but assume if I got out of it, i'd be back to the first error I got.

If it were working and I understand the code at all, is this intended to ask me how many rows I want to jump but offer a default value of 607 in that input box?

And presumably if I get to the point where it is running I will just be able to hit the return button with the 607 default in the input box and it will jump.

thanks,

confused in Rhode Island

[JBeaucaire]

I apologize if the Mac version of the script writer is different. I have no experience with Mac.

So I pasted the code, including the header
"Dim Rws as Long"

This is not a header, it is a variable declaration. Rws is the variable I plan to use and Long is the type, in this case it means any whole number up to a limit in the millions somewhere.

The rest of your understanding and comments are essentially correct.

Here's a sample workbook with the macro installed and functioning on hotkey CTRL-SHIFT-G. If this works in this sample workbook, perhaps you would be able to successfully just copy the macro into your own workbook.