Using variables in macro functions

[jb]

I'm trying to use variables and logic in some of Excel's methods & functions.
I'm not sure how to say this, so I'll use an example.

If I use the Macro record of Excel, and select a range or cell, or say goto
a cell or range I get something like this:

Range("O65").Select
Application.Goto Reference:="R18C5"

What I'd like to be able to do is to use these statements, with cell
addresses that I calculate baseed on my own variables.

Rownum = 5
Colnum = 10

Like in my imagined language, something like:
Range(rownum, colnum).select
Application.Goto Reference:="R"&rownum&"C"&colnum

Does anyone get what I'm saying and know how to do this.

 

[NickHK]

You were close:

Private Sub CommandButton2_Click()
Dim RowNum As Long
Dim ColNum As Long

RowNum = 5
ColNum = 10

Cells(RowNum, ColNum).Select
Application.Goto Reference:="R" & RowNum & "C" & ColNum

End Sub

A couple of points to bear in mind:
- It is seldom necessary to .Select an object before you work with it,
unless you want/need to user see what is happening
- Declare your variables. Preferably use Option Explicit. Check the help on
this.
- Qualify the range intended, otherwise you may find expected behaviour.
e.g.
ThisWorkbook.Worksheets("Sheet 1").Range("O65")
ThisWorkbook.Worksheets(1).Cells(RowNum, ColNum)

NickHK

 

[OssieMac]

Hi jb,

Syntax is:-

Cells(Rownum,Colnum).Select

(Make sure you use Cells with an 's' on the end)

Regards,

OssieMac