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.

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.

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.

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.

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

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

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

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

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