Hi,

I'm attempting to use vlookup to determine whether a value in one
spreadsheet (S1) is in another (S2), so that I can add that value to S2
if it isn't there. I'm checking all the values in S1 with a Do
While...Loop statement. I've coded so that OnError for the VLookUp, it
goes to the code that adds the value. This works perfectly for the
first value that cannot be found in S2, but for the second I get a
run-time 1004 error - 'Unable to get the Vlookup property of the
WorksheetFunction class'. I think that Excel is having a problem
dealing with an error when one is, effectively, still 'open', so to
speak. Can anyone help at all? This is driving me mad!

Regards,

MasterGee


--
MasterGee
------------------------------------------------------------------------
MasterGee's Profile: http://www.excelforum.com/member.php...o&userid=26372
View this thread: http://www.excelforum.com/showthread...hreadid=396462

What is in your OnError code? Are you using the Resume keyword anywhere or
Err.Clear?
--

Simon


"MasterGee" wrote:


Hi,

I'm attempting to use vlookup to determine whether a value in one
spreadsheet (S1) is in another (S2), so that I can add that value to S2
if it isn't there. I'm checking all the values in S1 with a Do
While...Loop statement. I've coded so that OnError for the VLookUp, it
goes to the code that adds the value. This works perfectly for the
first value that cannot be found in S2, but for the second I get a
run-time 1004 error - 'Unable to get the Vlookup property of the
WorksheetFunction class'. I think that Excel is having a problem
dealing with an error when one is, effectively, still 'open', so to
speak. Can anyone help at all? This is driving me mad!

Regards,

MasterGee


--
MasterGee
------------------------------------------------------------------------
MasterGee's Profile: http://www.excelforum.com/member.php...o&userid=26372
View this thread: http://www.excelforum.com/showthread...hreadid=396462

Having thought some more about this, I think I had the same problems and so
used the FIND method instead. This either returns a Range object variable or
Nothing.
--

Simon


"Simon Letten" wrote:

What is in your OnError code? Are you using the Resume keyword anywhere or
Err.Clear?
--

Simon


"MasterGee" wrote:


Hi,

I'm attempting to use vlookup to determine whether a value in one
spreadsheet (S1) is in another (S2), so that I can add that value to S2
if it isn't there. I'm checking all the values in S1 with a Do
While...Loop statement. I've coded so that OnError for the VLookUp, it
goes to the code that adds the value. This works perfectly for the
first value that cannot be found in S2, but for the second I get a
run-time 1004 error - 'Unable to get the Vlookup property of the
WorksheetFunction class'. I think that Excel is having a problem
dealing with an error when one is, effectively, still 'open', so to
speak. Can anyone help at all? This is driving me mad!

Regards,

MasterGee


--
MasterGee
------------------------------------------------------------------------
MasterGee's Profile: http://www.excelforum.com/member.php...o&userid=26372
View this thread: http://www.excelforum.com/showthread...hreadid=396462

Hi Simon,

Firstly, many thanks for your reply.

My OnError code just adds the value in the first spreadsheet to th
second spreadsheet - there's no Err.Clear or Resume in there, as I'
not aware of these statements/Keywords. I'll certainly give them ag
though, as that sounds like the kind of solution I was expecting.

Never thought about using Find, as, if I do find the value, I have t
return an associated value from the second spreadsheet to the first
hence the Vlookup. However, if Err.Clear or Resume doesn't work, I'l
give that a go.

Once again, thanks ever so much. :

--
MasterGe
-----------------------------------------------------------------------
MasterGee's Profile: http://www.excelforum.com/member.php...fo&userid=2637
View this thread: http://www.excelforum.com/showthread.php?threadid=39646

I'd guess you didn't reset your error handling code correctly, too.

dim Res as variant
on error resume next
res = application.worksheetfunction.vlookup(----)
if err.number < 0 then
res = "Missing???"
err.clear
end if
on error goto 0

=========

But you can drop the .worksheetfunction portion and just check for an error:

dim res as variant
res = application.vlookup(....)
if iserror(res) then
res = "missing???"
end if

I find the application.vlookup() much easier to work with and easier to
understand.




MasterGee wrote:

Hi Simon,

Firstly, many thanks for your reply.

My OnError code just adds the value in the first spreadsheet to the
second spreadsheet - there's no Err.Clear or Resume in there, as I'm
not aware of these statements/Keywords. I'll certainly give them ago
though, as that sounds like the kind of solution I was expecting.

Never thought about using Find, as, if I do find the value, I have to
return an associated value from the second spreadsheet to the first -
hence the Vlookup. However, if Err.Clear or Resume doesn't work, I'll
give that a go.

Once again, thanks ever so much. :)

--
MasterGee
------------------------------------------------------------------------
MasterGee's Profile: http://www.excelforum.com/member.php...o&userid=26372
View this thread: http://www.excelforum.com/showthread...hreadid=396462

--

Dave Peterson