Use a cell value as Row and Column number

Question

I'm using the following code to try and select the cell at the intersection of MyRow and MyCol. The current values are MyRow: 6 and MyCol: 4 which should select D6.

I can't get the correct syntax for the last line which is where it fails:

Sub SelectCS()
    Dim MyRow As Integer
    Dim MyCol As Integer
    MyRow = Sheet1.Range("a4").Value
    MyCol = Sheet1.Range("a5").Value
    Range((MyRow), (MyCol)).Select
End Sub

score 3 · Answer 1 · edited May 23 '17 at 11:45

3

Range(Integer, Integer) isn't a valid call. You should use Cells instead:

Cells(MyRow, MyCol).Select

Note that you should probably declare MyRow and MyCol as Long if you're using them to represent rows and columns. You should also avoid using the global Range object (I'd assume that this is for Sheet1, so it should be Sheet1.Cells or Sheet1.Range), and unless this is for UI you should probably not be using Select either.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 02 '17 at 02:20

Comintern

21,855
5
33
80

Thank you very much for your prompt reply and advise. Works perfectly!! – JeffM Feb 02 '17 at 02:41

score 0 · Answer 2 · answered Feb 02 '17 at 15:11

0

Public Sub SelectCS()
    Dim MyRow As Integer
    Dim MyCol As Integer
    MyRow = Range("A1").Value
    MyCol = Range("B1").Value
   Cells(MyRow, MyCol).Select
End Sub

answered Feb 02 '17 at 15:11

zatbusch

324
2
10

Could you please explain your answer to add more value? – wmk Feb 02 '17 at 19:48

Use a cell value as Row and Column number

2 Answers2

Linked