Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
Hello,
I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
Are you sure VLOOKUP() is getting a match..If there is no match this will
return error. Application.WorkSheetFunction.Vlookup() If this post helps click Yes --------------- Jacob Skaria "VBANovice" wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
Dear,
I am afraid this is definetly not a cure for my Ghost. There is already an On Error Resume Next part. Ghost in the Machine :) Anyhow, thanks for the effort. Regards, NoviceVBA "Jacob Skaria" wrote: Are you sure VLOOKUP() is getting a match..If there is no match this will return error. Application.WorkSheetFunction.Vlookup() If this post helps click Yes --------------- Jacob Skaria "VBANovice" wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
Say A1 contains:
cat and F1 thru G11 contain: dog 1 cat 2 mouse 3 rat 4 house 5 flea 6 flower 7 fish 8 tree 9 bush 10 car 11 then in the worksheet the formula: =VLOOKUP(A1,F1:G11,2,FALSE) will return a 2 In VBA, use Application and not Application.Worksheet function. Also make sure Vlookup understands the type of the arguments: Sub vlookupDemo() Range("B9").Value = Application.VLookup(Range("A1"), Range("F1:G11"), 2, 0) End Sub will also find the same 2 -- Gary''s Student - gsnu200854 "VBANovice" wrote: Dear, I am afraid this is definetly not a cure for my Ghost. There is already an On Error Resume Next part. Ghost in the Machine :) Anyhow, thanks for the effort. Regards, NoviceVBA "Jacob Skaria" wrote: Are you sure VLOOKUP() is getting a match..If there is no match this will return error. Application.WorkSheetFunction.Vlookup() If this post helps click Yes --------------- Jacob Skaria "VBANovice" wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
There is a difference with a few functions on how errors are treated between:
Application.vlookup() (and application.match()) and application.worksheetfunction.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then msgbox "no match" else msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then err.clear msgbox "no match" else msgbox res end if on error goto 0 ========= And since it looks like you're trying to find if there's a match (since you bring back what's in column 1 of the table). I'd use: Dim res as variant res = application.match(cRegion, rngfsc.columns(1),0) if iserror(res) then 'not there else 'it's on row # Res of the first column of rngfsc. end if VBANovice wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
no real need to test the worksheetfunction for an error as by default, the
value will be zero dim res as long on error resume next res = application.vlookup("someval", somerangehere, 2, false) if res = 0 then msgbox "no match" else msgbox res end if on error goto 0 "Dave Peterson" wrote in message ... There is a difference with a few functions on how errors are treated between: Application.vlookup() (and application.match()) and application.worksheetfunction.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then msgbox "no match" else msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then err.clear msgbox "no match" else msgbox res end if on error goto 0 ========= And since it looks like you're trying to find if there's a match (since you bring back what's in column 1 of the table). I'd use: Dim res as variant res = application.match(cRegion, rngfsc.columns(1),0) if iserror(res) then 'not there else 'it's on row # Res of the first column of rngfsc. end if VBANovice wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
I wouldn't do this.
What happens if the value that should be returned is 0? And if you don't change res to 0 before you do the =vlookup(), it won't change to 0. It'll still be what it was befo Dim res As Long res = 111 On Error Resume Next res = Application.VLookup("someval", somerangehere, 2, False) If res = 0 Then MsgBox "no match" Else MsgBox res End If On Error GoTo 0 Patrick Molloy wrote: no real need to test the worksheetfunction for an error as by default, the value will be zero dim res as long on error resume next res = application.vlookup("someval", somerangehere, 2, false) if res = 0 then msgbox "no match" else msgbox res end if on error goto 0 "Dave Peterson" wrote in message ... There is a difference with a few functions on how errors are treated between: Application.vlookup() (and application.match()) and application.worksheetfunction.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then msgbox "no match" else msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then err.clear msgbox "no match" else msgbox res end if on error goto 0 ========= And since it looks like you're trying to find if there's a match (since you bring back what's in column 1 of the table). I'd use: Dim res as variant res = application.match(cRegion, rngfsc.columns(1),0) if iserror(res) then 'not there else 'it's on row # Res of the first column of rngfsc. end if VBANovice wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
mine was a suggestion only, to open up ideas.
generally I'd wrap all worksheet functions that raise errors in a VBA function. I accept your argument re zero values, however, when returning an error its not difficult to decide upon a number like -999 or text that denotes an issue...after all, the developer should know what is expected and should be able to decide what constitutes good error handling and housekeeping. "Dave Peterson" wrote in message ... I wouldn't do this. What happens if the value that should be returned is 0? And if you don't change res to 0 before you do the =vlookup(), it won't change to 0. It'll still be what it was befo Dim res As Long res = 111 On Error Resume Next res = Application.VLookup("someval", somerangehere, 2, False) If res = 0 Then MsgBox "no match" Else MsgBox res End If On Error GoTo 0 Patrick Molloy wrote: no real need to test the worksheetfunction for an error as by default, the value will be zero dim res as long on error resume next res = application.vlookup("someval", somerangehere, 2, false) if res = 0 then msgbox "no match" else msgbox res end if on error goto 0 "Dave Peterson" wrote in message ... There is a difference with a few functions on how errors are treated between: Application.vlookup() (and application.match()) and application.worksheetfunction.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then msgbox "no match" else msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then err.clear msgbox "no match" else msgbox res end if on error goto 0 ========= And since it looks like you're trying to find if there's a match (since you bring back what's in column 1 of the table). I'd use: Dim res as variant res = application.match(cRegion, rngfsc.columns(1),0) if iserror(res) then 'not there else 'it's on row # Res of the first column of rngfsc. end if VBANovice wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble
Dear Gurus,
Thank you for your replies and efforts. I have the Vlookup statement working. Thanks for the full in depth tutorials. Very helpfull yet, doesnt help solving the issue. Still it is not working. The problem ( in simplified wording :) is ; I have a code working in one laptop. In machine A, the code is running with the same data sheets. In machine B, the code is not working with the same data sheet. They have both XP Pro OS, 97 and 2007 Excel versions loaded. I receive the 13 Error Code when I run your lines of code, where I expected not to get any error messages because you have included "On Error" statements. I understand that the On Error statement is not taking charge of the errors in the machine B. I am trying to understand why ? and how to resolve this issue. I tried to reinstall Excel but it did not solve the problem either. Still if I am asking for your help and time for a cure to my Ghost in the machine. Thanks and regards, VBANovice. "Patrick Molloy" wrote: no real need to test the worksheetfunction for an error as by default, the value will be zero dim res as long on error resume next res = application.vlookup("someval", somerangehere, 2, false) if res = 0 then msgbox "no match" else msgbox res end if on error goto 0 "Dave Peterson" wrote in message ... There is a difference with a few functions on how errors are treated between: Application.vlookup() (and application.match()) and application.worksheetfunction.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then msgbox "no match" else msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then err.clear msgbox "no match" else msgbox res end if on error goto 0 ========= And since it looks like you're trying to find if there's a match (since you bring back what's in column 1 of the table). I'd use: Dim res as variant res = application.match(cRegion, rngfsc.columns(1),0) if iserror(res) then 'not there else 'it's on row # Res of the first column of rngfsc. end if VBANovice wrote: Hello, I have a code that is looking for a Vlookup. I know that some of the queries are N/A# and to have On Error Resume Next error handling. The code is perfectly working in one computer and is generating "1004 run time error : Unable to get the Vlookup propert of the WorksheetFunction class error" in another. I have reloaded the Excel and Repaired, Diagnosed Excel, restored 2-3 days before from the backup. None yielded any cure. I work on XP Pro, have both 2003 & 2007 version generate the same fault. It seems as if On Error Resume is doomed. Any suggestions or perhaps remedies mostly welcomed and highly appreciated. Regards, VBA Novice. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do I get VLookup error 1004 | Excel Discussion (Misc queries) | |||
Ghost in the Machine - 1004 Vlookup & On Error Duo Trouble | Excel Worksheet Functions | |||
Error 1004 in WorksheetFunction.VLookup | Excel Programming | |||
VLOOKUP error 1004 | Excel Programming | |||
VBA VLookup Problem: Run-Time error '1004' | Excel Programming |