Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 --------------- Jacob Skaria "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
V or H Lookup
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) On Thu, 16 Apr 2009 03:45:02 -0700, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |