Urgent VB Macro help!

[bigb]

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&quot.Offset(counter, 0).Value = "0A"
Else
Range("X5&quot.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

 

[cactus13]

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&quot

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!

 

[msmith]

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

 

[bigb]

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