Following calculation, a macro returns to the next cell

Posted by Frank Krumm on November 22, 2001 8:59 PM

I have a macro to run a calculation using a keyboard shortcut. Presently the macro returns me to A1 following the calculation, whereupon I may enter the next value.

What I would like is to place the input data into ranges A1:A20, and have the macro then return to the next cell, so I can use the keyboard shortcut without having to enter the data manually, press enter, and then use the shortcut.

Thanks for the help!

Posted by peter on November 22, 2001 10:52 PM

Could you supply a bit more info
So far i have
A
1 10
2 20
3 30
4
5
What do you do in the keyboard shortcut?

Posted by Walter on November 22, 2001 11:08 PM

1) If the macro change the active cell to A1, you must have something like Cells(1,1) or Range("A1") .Select or .Activate in your macro. If that's the case, just remove them or substitute them with other referencing method.

2) Or, add a variable to remember current cell address so that you can jump to next cell just before the macro end.

Sub ...()
Dim S
S = Selection.Address
... (original macro)
Range(S).Offset(1,0).Select
End Sub

3) Or, if you want macro to operate on a number of cells on one key stroke, you can use For Each command, i.e.,

Sub ...()
For Each S in Selection
... (macro operating on cell S)
Next S
End Sub

Then, you can just select all the value you want to calculate and call the macro. The macro would than operate on each cell and calculate the value for you.

Posted by Frank Krumm on November 23, 2001 8:33 PM

Beautiful! Thank you very much Walter. Both are #1 EOM