-2

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!

Sathish Kothandam
  • 1,530
  • 3
  • 16
  • 34
samuraiY
  • 35
  • 1
  • 2
  • 6
  • 4
    Welcome to StackOverflow. A simple search here of "vba excel select cell" should turn up literally dozens of answers to this question. Please at least try to find a solution yourself before posting a question here, as chances are quite good someone has asked before and gotten an answer. Thanks. – Ken White Sep 26 '13 at 01:45
  • Look into the R1C1 syntax if you want to refer to columns by their index. – Mathieu Guindon Sep 26 '13 at 01:46
  • Take a look at this: http://support.microsoft.com/kb/291308 – NoChance Sep 26 '13 at 01:49
  • Thanks Emmad! Exactly what I was looking for. Ken - I did search and found simmilar info but not exactly what I was looking for. – samuraiY Sep 26 '13 at 01:57
  • This is because `ActiveCell` literally returns a `Range` object that represents the active cell in the active window. So `ActiveCell(e,i)` is basically equal to `ActiveCell.Offset(e-1,i-1)`. If your orginal active cell is not A1, of course your code will refer to the wrong cell – sam092 Sep 26 '13 at 03:22

1 Answers1

4

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 :)

enter image description here