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