×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Urgent VB Macro help!

Urgent VB Macro help!

Urgent VB Macro help!

(OP)
Hi

I am having problem with this statement. Can you help me rewrite it?

If (IsNA(WorksheetFunction.VLookup(r, Range4, 2, False))) Then
        Range("X5").Offset(counter, 0).Value = "0A"
Else
    Range("X5").Offset(counter, 0).Value = "0B"
End If

Note: Range4 is  a lookup in a range in another sheet.


IsNA is giving problem says Sub or Function not defined.
I need to check if this vlookup returns a value else it should lookup some thing else.
Can you tell me what to do?
Even  this does not work:

If (WorksheetFunction.IsNA(WorksheetFunction.VLookup(r, Range4, 2, False))) Then

Please advice.
Thanks!!!!
BigB

RE: Urgent VB Macro help!

Well I don't know if this helps you any further, but I think one of the problems could be that your variables are not (correctly) assigned. This results in problems with your VLOOKUP routine.

If I look at the syntaxis by means of the help provided with Excel then you would see the following:

=VLOOKUP(SearchValue, Table, ColumnIndex, Approximate)

SearchValue should be of the right type, string if you're looking for text, numerical if your looking for digits, etc. There could be a problem with a leading space if you are converting values in your code (for example when you use Asc() to convert a numeric value into a string)

Table is a RANGE. If you did not dimension range4 as a range then there could be the problem. To do so add the following statement to your code:

Dim range4 as Range

To set the value of range use something like this:

Set range4 = Range("A2:C6")

Replace A2:C6 for the correct range you want your lookup to be done to.

ColumnIndex is an INTEGER, so any normal positive numeric value smaller or equal to the total numers of columns in your selection would do.

Approximate is a BOOLEAN value, either True or False, it determines if an exact (False) or a best match (True) should be found.

I think the problem is in the VLOOKUP function. Have you already tried it outside the code, just by entering the correct values and testing it in a worksheet instead of entering it by code? That's a way to see if the problem is in your VLOOKUP or not.

Good luck!

RE: Urgent VB Macro help!

BigB,

First, IvyMike's response to this same question in the Engineering Spreadsheets Forum should work, based on VBA documentation in Excel.  However, I also ran into errors using this (not necessarily the same error you reported).  For whatever reason, I was able to get the following code to work:

If (Application.IsNA(Application.VLookup(r, Range4, 2, False))) Then
  Range("A5").Offset(counter, 0).Value = "0A"
Else
  Range("A5").Offset(counter, 0).Value = "0B"
End If


Let us know if this works.  Btw, I'm using Excel 97

RE: Urgent VB Macro help!

(OP)
Hi msmith
Yup it works!
I guess I was using WorksheetFunction.IsNA instead of Application.IsNA. Now it works .
Thanks!
Bigb

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


Resources


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