VBA and Lookup

[JoelTXCive]

Dear All:

I am a novice programmer and have a conceptual question or two on a project I am working on.

I have a sparsely populated table with ranges of values. For example Row 1 has range 0 to 10, row 2 has 11 to 15, row 3 has 16 to 30, etc, etc.

Each range has data values in columns, but these data values differ by row. For example, one row might have 4 columns of attributes attached, and another row only has 2 columns of data attached.

It is a dynamic table where the range sizes and the number of rows can change. The max number of rows I can envision is 15 though. (so It’s not a huge table)

I would like to write a VBA function to take a lookup value, and then perform specified operations depending on what row is found. I don’t think I can use a normal VLlookup because different mathematic operations will be performed depending on what row is selected.

I am thinking I can somehow use VBA’s select case function, but my questions are:

1) Do I need to bring the entire table into VBA as a sparsely populated matrix? Or can I ‘leave it on the spreadsheet’ somehow?

2) Is Select Case the best way to attack this problem? Since my number of rows and range size are dynamic, how do I handle that?

I’m not sure this helps or not, but its bridge design data. For example, the first 1000 feet of a bridge are on a vertical curve, so there are curve coefficients used to do the math for the curve. The next 300 feet might have a constant slope, and then the following 600 feet might enter another vertical curve. I want to be able to enter a location such as ‘346ft’ and have excel employ the proper equation to determine the roadway elevation at that point. (we have CAD software that gives us these values, but need to manually check the numbers)

Thank you!

 

[SkipVought]

Hi,

1) either way. If you're comfortable with the Excel Object Model and the associated VBA properties and methods, then using the values on the sheet is fine. Otherwise, put the values in an array.

2) I like the clarity and simplicity (in most cases, not to be punny) of Select Case...End Select.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}