ok..
I'm trying to get the value of a specific elements of a named range in VBA.
thinking something like [RangeName](element) or [RangeName].element, but can't seem to quite get the syntax right. The element must be a random integer between 1 and [RangeName].count. I already have a vba equivalent for the RandBetween worksheet function.
background: I'm working on some data randomization routines to generate test data for a project. I have several lists of valid elements: First Name, Last Name, location, element type, etc.
Lets talk about names as an example. Given a dynamic named range that points to Last Names [LU_LName] with (currently) 5000+ last names.
There is a design requirement is that LookUp (LU) ranges be dynamic to allow for adding/deleting elements without breaking things...
In a spreadsheet view, I can simply enter a formula:
"=index([LU_LName],int(randbetween(1,counta([LU_LName])))"
Which gives me a random element from the LU_LName list.
This works great, but is slow, especially with large data set generation, and changes/recalculates everytime any element on the sheet updates.
Consequently, I'm trying to convert the process to VBA. Today, I can select the number of rows to meet the desired data-set size, and populate the cells with the proper formula. Much faster, especially when I wrap it with screenupdating=off/on and completing with copy used range, paste values.. but still slow with larger data sets (desired data sets can range from 1 to 10's of thousands...)
I'm trying to get the value of a specific elements of a named range in VBA.
thinking something like [RangeName](element) or [RangeName].element, but can't seem to quite get the syntax right. The element must be a random integer between 1 and [RangeName].count. I already have a vba equivalent for the RandBetween worksheet function.
background: I'm working on some data randomization routines to generate test data for a project. I have several lists of valid elements: First Name, Last Name, location, element type, etc.
Lets talk about names as an example. Given a dynamic named range that points to Last Names [LU_LName] with (currently) 5000+ last names.
There is a design requirement is that LookUp (LU) ranges be dynamic to allow for adding/deleting elements without breaking things...
In a spreadsheet view, I can simply enter a formula:
"=index([LU_LName],int(randbetween(1,counta([LU_LName])))"
Which gives me a random element from the LU_LName list.
This works great, but is slow, especially with large data set generation, and changes/recalculates everytime any element on the sheet updates.
Consequently, I'm trying to convert the process to VBA. Today, I can select the number of rows to meet the desired data-set size, and populate the cells with the proper formula. Much faster, especially when I wrap it with screenupdating=off/on and completing with copy used range, paste values.. but still slow with larger data sets (desired data sets can range from 1 to 10's of thousands...)
