Dynamic Select Case routines

[showard1]

Hi all

I have a question on select case statements, before I embark on this particular useful function. I am working in excel 2010.

Can VBA cope with what I would descibe as a dynamic select case statement ?

To try and explian, I have a series of arrays that are dimensioned according to a number of filled cells on a reference sheet. It could have 5 to 10 elements (in 1 dimension only)

If there are 5 elements then there will be 5 select case lines, if 7 elements in the array then 7 select case line, etc.

This is part of a dynamic analysis tool where the user will input the data limits within which the data is to be analysed eg

select case value
case is <100
  do something
case 101 to 500
  do something
case 500 to 1000
  do something
end select

the user will have input 100, 500, and 1000 in a table, however if they had also input 750 in the table and extra line would be used, therefore the code will now look like:

select case value
case is <100
  do something
case 101 to 500
  do something
case 500 to 750
  do somthing
case 750 to 1000
  do something
end select

any help or guidance would be greatly appreciated.

Simon

 

[Norie]

If you mean dynamically change the no of cases then the short answer is no.

You could change the values used in the cases but I don't think that would help.

What's the 'something' you want to do for each case?

PS You could write code to write code I suppose.

 

[showard1]

Thanks for the input, I was starting to expect as much. and no I dont think I want to start writing code modifying code, thats going to go to far.

For info the "do something" part is a series of calculations according to which grouping they fall into. All of this I was doing within the arrays and populating others so the user gets the analysis in a sumary table at the end of the routine. The do something bit is fairly simple for me, but is quite complex if the user had to see it.

I will try and figure out an alternative way, any other suggestions from anyone on a way around this would be interesting

 

[Norie]

Is the something basically the same for each case apart from the values involved in the calculation?

 

[showard1]

Yes, however there are some subtle changes in the first and last statement, and the array elements referenced change as we work down the function

 

[Norie]

What I was thinking of was some sort of looping.

Not sure what type of looping or if it would be appropriate though.