Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

VBA and Lookup

VBA and Lookup

VBA and Lookup

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


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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close