Here are examples of normal VBA with no variables that works:

Range("K20").Select
Range("K20").Activate

I want to replace the 20 (meaning row 20) with a variable from a named cell.

For example, I named cell Z100 as "XRow". XRow now contains the number 7, which can change any time according to formula. I want the VBA to say something like one of the following, but I can't get it to work:

Range("KXrow").Select
Range("K"Xrow"").Select
Range(Cells(K,Xrow)).Select
Range(Cells(K,"Xrow")).Select

If necessary, I could make do with an unnamed cell, so that it could look something like

Range("KZ100").Select
Range("K"Z100"").Select
Range(Cells(K,Z100)).Select
Range(Cells(K,"Z100")).Select

Any ideas? Thanks!

Dim lngRow as Long
lngRow = Range("XRow").Value


Range("K" & lngRow).Select
-or-
Cells(11, lngRow).Select
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel: Date picker, Clean Data, Classic Menu ...)




"David Godinger"
wrote in message
news:20912655.1748.1332623461737.JavaMail.geo-discussion-forums@pbtd1...
Here are examples of normal VBA with no variables that works:

Range("K20").Select
Range("K20").Activate

I want to replace the 20 (meaning row 20) with a variable from a named cell.

For example, I named cell Z100 as "XRow". XRow now contains the number 7, which can change any
time according to formula. I want the VBA to say something like one of the following, but I can't
get it to work:

Range("KXrow").Select
Range("K"Xrow"").Select
Range(Cells(K,Xrow)).Select
Range(Cells(K,"Xrow")).Select

If necessary, I could make do with an unnamed cell, so that it could look something like

Range("KZ100").Select
Range("K"Z100"").Select
Range(Cells(K,Z100)).Select
Range(Cells(K,"Z100")).Select

Any ideas? Thanks!

Works great! Nice and simple.

Thanks!


On Saturday, March 24, 2012 3:33:30 PM UTC-7, Jim Cone wrote:
Dim lngRow as Long
lngRow = Range("XRow").Value


Range("K" & lngRow).Select
-or-
Cells(11, lngRow).Select

On Sat, 24 Mar 2012, Jim Cone wrote:

Dim lngRow as Long
lngRow = Range("XRow").Value


Range("K" & lngRow).Select
-or-
Cells(11, lngRow).Select

It can be even simpler. Like:

Range("K" & Range("XRow").Value).Select
-or-
Cells(11, Range("XRow").Value).Select

Don. www.donwiss.com (e-mail link at home page bottom).

In this code, we first declare a variable `XRow` as a Long data type. We then assign the value from the named cell "Z100" to the `XRow` variable using the property.

Next, we use the code to select the cell in column K with the row number from the `XRow` variable. The & symbol is used to concatenate the letter "K" with the value of the `XRow` variable.

To select a cell in VBA with a variable row number, you can follow these steps:

1. Declare a variable to hold the row number as a Long data type.
2. Assign the value from the named cell containing the row number to the variable using the
   Code:

   Range("named_cell").Value

   property.
3. Use the
   Code:

   Range("column_letter" & row_number_variable).Select

   code to select the cell in the desired column with the row number from the variable.