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" wrote:

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" wrote:

or maybe this

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

Mike

"Mike H" wrote:

Try it like this

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

Mike

"Risky Dave" wrote:

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".?

"Jacob Skaria" wrote:

It should be Worksheetfunction.VLookup

If this post helps click Yes
---------------
Jacob Skaria


"Risky Dave" wrote:

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