Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
Below is what I've writtem so far: Sub vlookup() Dim myrange As Range Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list") fred = Application.WorksheetFunction.vlookup(Range("a1"), myrange, 2, True) Range("b1") = fred End Sub The contents of "first.xls" has numbers 1 to 10 in column A and letters a to i in column B. My issue is if I change the 'true' value to 'false' within the vlookup function. It works fine until I enter a value that is not in my list, I get a 'error 1004 unable to get the vlookup property of the worksheetfunction class' message box come up. I am expecting a '#N/A' instead. I'm using excel 2003 with vb 6.3. Any ideas? -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
Sub vlookup()
Dim myrange As Range dim Fred as variant Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list") fred = Application.vlookup(Range("a1"), myrange, 2, True) if iserror(fred) then range("B1") = "missing" else range("B1") = fred end if End Sub I dropped the .worksheetfunction from your code. Application.vlookup() returns an error that you can check for. Application.worksheetfunction.vlookup() causes a run time error (if no match) that you have to code around. on error resume next fred =Application.WorksheetFunction.vlookup(Range("a1") , myrange, 2, True) if err.number < 0 then fred = "missing" err.clear end if range("B1").value = fred === I find the application.vlookup() easier. pjjclark wrote: Below is what I've writtem so far: Sub vlookup() Dim myrange As Range Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list") fred = Application.WorksheetFunction.vlookup(Range("a1"), myrange, 2, True) Range("b1") = fred End Sub The contents of "first.xls" has numbers 1 to 10 in column A and letters a to i in column B. My issue is if I change the 'true' value to 'false' within the vlookup function. It works fine until I enter a value that is not in my list, I get a 'error 1004 unable to get the vlookup property of the worksheetfunction class' message box come up. I am expecting a '#N/A' instead. I'm using excel 2003 with vb 6.3. Any ideas? -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
Using worksheetfunction as a qualifier causes it to raise a trappable error
rather than return #N/A, so trap the error. Sub vlookup() Dim myrange As Range On Error goto ErrHandler Set myrange =Workbooks("first.xls").Worksheets("sheet1").Range ("list") fred = Application.WorksheetFunction.vlookup( _ Range("a1"), myrange, 2,False) Range("b1") = fred Exit Sub ErrHandler: msgbox Range("A1") & " was not found End Sub -- Regards, Tom Ogilvy "pjjclark" wrote in message ... Below is what I've writtem so far: Sub vlookup() Dim myrange As Range Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list") fred = Application.WorksheetFunction.vlookup(Range("a1"), myrange, 2, True) Range("b1") = fred End Sub The contents of "first.xls" has numbers 1 to 10 in column A and letters a to i in column B. My issue is if I change the 'true' value to 'false' within the vlookup function. It works fine until I enter a value that is not in my list, I get a 'error 1004 unable to get the vlookup property of the worksheetfunction class' message box come up. I am expecting a '#N/A' instead. I'm using excel 2003 with vb 6.3. Any ideas? -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
And after reading Don's message, I noticed that he changed the sub's name.
I missed that. I don't think it's a good idea to use a name of a worksheet function as your sub's name. Dave Peterson wrote: Sub vlookup() Dim myrange As Range dim Fred as variant Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list") fred = Application.vlookup(Range("a1"), myrange, 2, True) if iserror(fred) then range("B1") = "missing" else range("B1") = fred end if End Sub I dropped the .worksheetfunction from your code. Application.vlookup() returns an error that you can check for. Application.worksheetfunction.vlookup() causes a run time error (if no match) that you have to code around. on error resume next fred =Application.WorksheetFunction.vlookup(Range("a1") , myrange, 2, True) if err.number < 0 then fred = "missing" err.clear end if range("B1").value = fred === I find the application.vlookup() easier. pjjclark wrote: Below is what I've writtem so far: Sub vlookup() Dim myrange As Range Set myrange = Workbooks("first.xls").Worksheets("sheet1").Range( "list") fred = Application.WorksheetFunction.vlookup(Range("a1"), myrange, 2, True) Range("b1") = fred End Sub The contents of "first.xls" has numbers 1 to 10 in column A and letters a to i in column B. My issue is if I change the 'true' value to 'false' within the vlookup function. It works fine until I enter a value that is not in my list, I get a 'error 1004 unable to get the vlookup property of the worksheetfunction class' message box come up. I am expecting a '#N/A' instead. I'm using excel 2003 with vb 6.3. Any ideas? -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
Thanks for your help. It works, at last I can now stop banging my head!! -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
Just for information if you are actually trying to learn something:
I your look at Don's code, the reason it works is because he used application.Vlookup rather than Application.worksheetFunction.Vlookup. If A1 is blank that would be problematic as well, but he did nothing in his code to correct for that - only made it slower by replacing Range("A1") with [a1] -- Regards, Tom Ogilvy "pjjclark" wrote in message ... Thanks for your help. It works, at last I can now stop banging my head!! -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
I just re-tested. If a1 is blank you still get n/a. Isn't this what OP asked
for? I only used [a1] for brevity in the formula part and didn't notice an appreciable change in speed. Perhaps this is more important with very slow computers with little memory. <G -- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just for information if you are actually trying to learn something: I your look at Don's code, the reason it works is because he used application.Vlookup rather than Application.worksheetFunction.Vlookup. If A1 is blank that would be problematic as well, but he did nothing in his code to correct for that - only made it slower by replacing Range("A1") with [a1] -- Regards, Tom Ogilvy "pjjclark" wrote in message ... Thanks for your help. It works, at last I can now stop banging my head!! -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup, true false issue
and if A1 isn't blank and you put in a non matching value it puts in n/a as
well, so being blank or not is not part of the issue and had no bearing on the original problem. Sorry you had to exert extra time retesting. Using [a1] instead of Range("A1") had no bearing on solving the problem as well. You like to use it, so use it. If you disagree that using application.Vlookup vice WorksheetFunction.Vlookup elimintates the 1004 error on a non match please enlighten us. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... I just re-tested. If a1 is blank you still get n/a. Isn't this what OP asked for? I only used [a1] for brevity in the formula part and didn't notice an appreciable change in speed. Perhaps this is more important with very slow computers with little memory. <G -- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just for information if you are actually trying to learn something: I your look at Don's code, the reason it works is because he used application.Vlookup rather than Application.worksheetFunction.Vlookup. If A1 is blank that would be problematic as well, but he did nothing in his code to correct for that - only made it slower by replacing Range("A1") with [a1] -- Regards, Tom Ogilvy "pjjclark" wrote in message ... Thanks for your help. It works, at last I can now stop banging my head!! -- pjjclark ------------------------------------------------------------------------ pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180 View this thread: http://www.excelforum.com/showthread...hreadid=508959 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's the best way to toggle between true and false in Excel? Hiall, My excel work involves a lot of toggling between true and false (booleantypes) ... and it's very repetitive... Is there a way to select a bunch ofcells, and press a key short-cu | Excel Discussion (Misc queries) | |||
Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
Reverse false and combine with true true value | Excel Worksheet Functions | |||
True Or False, no matter what... it still displays the false statement | Excel Programming |