Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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!
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,549
Default 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!



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 300
Default 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).
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Select A Cell in VBA with a Variable Row Number? [email protected] Excel Programming 0 March 24th 12 09:30 PM
select cell by variable name Adam Excel Discussion (Misc queries) 6 May 20th 08 07:54 PM
Select Variable Number of Columns Mike H. Excel Programming 4 July 9th 07 05:46 AM
Select Cell based on Variable address achidsey Excel Programming 2 September 17th 05 06:47 PM


All times are GMT +1. The time now is 06:54 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"