422,363 Members | 2,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,363 IT Pros & Developers. It's quick & easy.

Use a string to represent a range object

gnawoncents
100+
P: 212
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!
3 Weeks Ago #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 909
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"
3 Weeks Ago #2

Expert 100+
P: 909
sorry, duplicate posting.. ;(
3 Weeks Ago #3

Post your reply

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