Simple Excel Macro Question

Question

This should be simple, not sure why I am having trouble with this..... In a data input range that a user is entering data there is a "left" and a "right" side of the form. Not all the data in any given row has to be filled in. I want to create a macro (and assign it to a hot key I.E. ctrl-z ) such that if it is hit it will position the active cell to be in the current row and in column "X". in other words hitting the ctrl-Z will cause the cursor to jump to column "X"

Answer 1

In this case column "x" is actually column E:

Sub ToColE()
    Const colE = 5
    Dim offset As Integer
    offset = colE - ActiveCell.Column
    ActiveCell.offset(rowOffset:=0, columnOffset:=offset).Activate
End Sub

Answer 2

Either of the following statements should do the trick. Assign the macro shortcut from the Tools Menu --> Macros.

Public Sub SelectStuff()
    Cells(3, 5).Select
    Rows(17).Select
End Sub

Answer 3

Try this

Sub MoveLeftOnForm()
    Dim rn As Integer
    rn = ActiveCell.Row
    Range("B" & rn).Select
End Sub

it assumes B is your leftmost column in the form.

Answer 4

This code should do it for you:

Public Sub SetUpKeyboardShortcut()
    Application.OnKey "^z", "SelectColInSelectedRow"
End Sub

Public Sub SelectColInSelectedRow()
    Const ColToSelect = "X"
    Range(ColToSelect & CStr(Selection.Row)).Select
End Sub

Just call SetUpKeyboardShortcut in the Workbook_Open event in the ThisWorkbook module. I believe the code above needs to be stored in a standard module.

Hope that helps...

Answer 5

This will work:

Public Sub go()
Cells(ActiveCell.Row, 5).Activate
End Sub

Just substitute the "5" with whatever column number you wish. You should be able to assign a ctrl+z hotkey from the options on the macro menu.