468,315 Members | 1,519 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,315 developers. It's quick & easy.

Use a string to represent a range object

gnawoncents
214 100+
Greetings,

I am trying to find out if something is possible in Excel VBA. I would like to use a string or integer to modify/represent the name of a range object. Please note that I do not have a range stored in string format.

For example, I have the following range objects already set: rng1, rng2, rng3, etc.

I am looping through rows and want to change the associated range with each iteration, using either a string or integer. Below is a sample of the code (not working) I need help with.

Expand|Select|Wrap|Line Numbers
  1. Dim loImmobile As ListObject
  2. Dim rowImmobile As ListRow
  3. Dim shtImmobile As Worksheet
  4. Dim rngPlace As Range
  5. Dim intCount As Integer
  6.  
  7. Set shtImmobile = Worksheets("shtImmobile")
  8. Set loImmobile = shtImmobile.ListObjects("tblImmobile")
  9. intCount = 5
  10.  
  11. For Each rowImmobile In loImmobile.ListRows
  12.         rngPlace = RandCell(Rng(intCount))
  13.        'Do things with rngPlace
  14.         intCount = intCount + 1
  15. Next rowImmobile
  16.  
  17.  
The problem area is in the line below. RandCell calls a function that retrieves a random cell from the given range.
Expand|Select|Wrap|Line Numbers
  1.         rngPlace = RandCell(Rng(intCount))
  2.  
Thank you for your help!
Mar 31 '18 #1
2 2932
Luuk
1,043 Expert 1GB
With the line below you can select all kind of range's
Expand|Select|Wrap|Line Numbers
  1. Range(Cells(row1, col1), Cells(row2, col2)).Select
With the values:
row1=2, col1=2, row2=4, col2=5,
You will select the range "B2:E5"
Apr 2 '18 #2
Luuk
1,043 Expert 1GB
sorry, duplicate posting.. ;(
Apr 2 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Cyper Hunter | last post: by
1 post views Thread by Thomas | last post: by
5 posts views Thread by miki | last post: by
1 post views Thread by Jeffrey Bradshaw | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.