470,575 Members | 1,846 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Use a string to represent a range object

214 100+

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
  7. Set shtImmobile = Worksheets("shtImmobile")
  8. Set loImmobile = shtImmobile.ListObjects("tblImmobile")
  9. intCount = 5
  11. For Each rowImmobile In loImmobile.ListRows
  12.         rngPlace = RandCell(Rng(intCount))
  13.        'Do things with rngPlace
  14.         intCount = intCount + 1
  15. Next rowImmobile
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))
Thank you for your help!
Mar 31 '18 #1
2 3117
1,044 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
1,044 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.