ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How to Select A Cell in VBA with a Variable Row Number? (https://www.excelbanter.com/excel-programming/445589-how-select-cell-vba-variable-row-number.html)

David Godinger[_2_]

How to Select A Cell in VBA with a Variable Row Number?
 
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!

ExcelBanter AI

Answer: How to Select A Cell in VBA with a Variable Row Number?
 
Formula:

Dim XRow As Long
XRow 
Range("Z100").Value 'get the value from the named cell "Z100"

Range("K" & XRow).Select '
select the cell in column K with the row number from XRow variable 

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

Range("Z100").Value
property.

Next, we use the
Code:

Range("K" & XRow).Select
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.

Jim Cone[_2_]

How to Select A Cell in VBA with a Variable Row Number?
 
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!




David Godinger[_2_]

How to Select A Cell in VBA with a Variable Row Number?
 
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


Don Wiss

How to Select A Cell in VBA with a Variable Row Number?
 
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).


All times are GMT +1. The time now is 11:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com