Selecting a cell with VBA in excel

Question

I am trying to select a cell in excel with vba. I want to be able to use to variables(i & e) to choose a cell. Where i = 3 and e = 13 I would like to be able to

ActiveCell(e, i).Activate

and it select cell C13.

ActiveCell(e, i).Activate

does work but only when original active cell is A1. Any help would be greatly appreciated!

Answer 1

I put comments in the code so it should be really easy to understand.

Run the below code first

Sub Main()

    Dim i As Long ' row variable
    Dim e As Long ' column variable

    i = 3 ' row 3
    e = 13 ' column 13 ("M")

    ' this will put: Cells(3,13) in Range "M3"
    Cells(i, e) = "Cells(" & i & ", " & e & ")"


    ' if you want to offset the current active cell then
    ' use Offset(x, y)

    '  use negative x to offset up
    Cells(i, e).Offset(-1, 0) = "1 up"

    '  use positive x to offset down
    Cells(i, e).Offset(1, 0) = "1 down"

    '  use negative y to offset left
    Cells(i, e).Offset(0, -1) = "1 left"

    '  use positive y to offset right
    Cells(i, e).Offset(0, 1) = "1 right"


    ' same principles apply when using range object
    Dim r As Range
    Set r = Cells(i, e)

    r.Offset(-2, 0) = "2 up"
    r.Offset(2, 0) = "2 down"
    r.Offset(0, -2) = "2 left"
    r.Offset(0, 2) = "2 right"

    Columns.AutoFit
End Sub

then look at your sheet and analyse what command does what :)