using cell content to identify range + array?

Hi,

I need help with something that I should probably know but can't seem to remember how to solve. I've got a piece of code that uses a For/Next loop to calculate the value of an object 'Total' based on values in various arrays:

Please Login or Register  to view this content.

And the code works great except that I'd like to be able to change the upper bound of R and the ArrA & ArrB array sizes based on the value in a worksheet cell (rather than having to change the coding each time that cell value changes). I know I can assign an object name to the cell's value (="Max") & can then say "For R = 1 to Max", but how to I use "Max+1" to describe the upper bounds of my two arrays so that R will loop through all the array entries & doesn't stop short or over-shoot the size of each array?? And how can I do that without having to use additional loops to define my two arrays?

I've been playing arround with solutions like:

Please Login or Register  to view this content.

But nothing has worked so far. And, since it's been a nearly a year since I wrote code, I can't remember if I can actually use UBound to define an array (or if I can only use it to loop through entries in the array) or if there's a better solution that I just haven't though of yet.

Can anyone help jog my memory and suggest a quick & easy solution?
I'd be very grateful for any help you can give!
Thx.

Hi

On the right track.

1) Don't use reserved words as variables. So instead of MAX, use something like myMAX or Ulim...

2) Set the array in the way you have mentioned

myMax = WB.Worksheets("Table").Range("N8").Value +1
ArrA = WB.Worksheets("Table").Range("K2:K" & myMax).Value

3) As long as both arrays are going to be the same size, then use the lbound and ubound to get the dimensions

for R = lbound(arra) to ubound(arra)
...
next R


hth

rylo

Thanks so much rylo. That worked like a charm!
I'm really grateful!