V or H Lookup

[Risky Dave]

Hi,

I am struggling to get Vlookup and Hlookup to work in my Office 2007
application.

For example the following does not generate an error, but also does not
identify when the value in column 10 is "Opportunity". The data is sorted in
ascending order as per the Vlookup requirement.

Dim sRiskNum As String ' risk number being costed
Dim sOppThreat As String ' used to define an Opportuinity or Threat

If Application.VLookup(sRiskNum,
Sheets("identification").Range("a6:j500"), 10) = "Opportunity" Then
sOppThreat = "Opportunity"
Else
sOppThreat = "Threat"
End If

Currently I am using a workaround of creating a Range variable offsetting
this through the list of sRiskNum within a Do...While loop and then returning
the appropriate value via another offset. Obviously, this is a more
complicated solution than using Vlookup.

I am having a similar problem elsewhere using Hlookup.

Can anyone tell me what is wrong with the way I have constructed the above
If statement and let me know how to get it working?

Many thanks in advance

Dave

 

[Jacob Skaria]

It should be Worksheetfunction.VLookup

If this post helps click Yes

 

[Risky Dave]

Jacob,

Thanks for the quick reply. I have changed "Application" to
"WorksheetFunction" as suggested and it is still returning "Threat" when it
should return "Opportunity".?

 

[Mike H]

Try it like this

On Error Resume Next
sOppThreat = WorksheetFunction.VLookup(sRiskNum,
Sheets("identification").Range("a6:j500"), 10)
If sOppThreat = "" Then sOppThreat = "Threat"

Mike

 

[Mike H]

or maybe this

If sOppThreat <> "opportunity" Then sOppThreat = "Threat"

Mike

 

[Jacob Skaria]

Try this

If WorksheetFunction.VLookup(sRiskNum,
Sheets("identification").Range("a6:j500"), 10) <> "Opportunity" Then
sOppThreat = "Threat"
Else
sOppThreat = "Opportunity"
End If

Also check the Case...

If this post helps click Yes

 

[Risky Dave]

Mike,

Thanks.

I've changed the code to:

sOppThreat = WorksheetFunction.VLookup(sRiskNum,
Sheets("identification").Range("a6:j500"), 10)

and it is now returning the last value in the range of valid sRiskNum rather
than the one actually being looked for?

 

[Mike H]

Hi,

In which case I'd check your data. The first reason your code could have
bombed using your IF loop is if it found no match in column A which is why I
included on error res.... etc.

If it's now 'missing' a value in column A then I suggest you check the
'match' being missed actually is a match i.e. no rogue spaces - numbers/text.

 

[Risky Dave]

Jacob, Mike,

Thanks for your help.

I'm not sure why it makes a differenc, but by using the "false2 switch at
the end of the stateemnt this now works!

Dave

 

[Chip Pearson]

If you're looking for an exact match with VLOOKUP, you should set the
4th parameter to False. Also, I would store the value returned by
VLOOKUP in a variable rather than directly comparing it to a value in
an If statement. Using the result directly in an If statement will
cause problems if VLOOKUP fails to find a match.

There are two ways of calling VLOOKUP in code. The first is to use the
WorksheetFunction class. E.g.,

Dim Res As Variant
On Error Resume Next
Res = Application.WorksheetFunction.VLookup( _
11, Range("A1:B5"), 2, False)
If Err.Number = 0 Then
Debug.Print "found"
Else
Debug.Print "not found"
End If

If VLOOKUP fails to find a match, a run-time exception is thrown and
so you must use an On Error statement, as shown above, to test for an
error.

You can omit the WorksheetFunction reference and call VLOOKUP directly
from the Application object. In this case, no error is thrown if
VLOOKUP fails to find a match. Instead, it returns an Error type
value, which you can store in a Variant and test with IsError.

Dim Res As Variant
Res = Application.VLookup( _
111, Range("A1:B5"), 2, False)
If IsError(Res) = False Then
Debug.Print "found"
Else
Debug.Print "not found"
End If

If you omit the 4th parameter or set it to a non-zero numeric value,
the data list must be in sorted order (ascending). Otherwise, the
VLOOKUP will likely return the wrong value.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)