Using variables in macro functions
 

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.

Using variables in macro functions
 

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

"jb" wrote in message
...
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.

Using variables in macro functions
 

Hi jb,

Syntax is:-

Cells(Rownum,Colnum).Select

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

Regards,

OssieMac

"jb" wrote:

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.