×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# VBA and Lookup

## VBA and Lookup

(OP)
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!

### RE: VBA and Lookup

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!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!