worksheetfunction.vlookup error

L

Larry Levinson

ActiveCell.Value = Application.WorksheetFunction.VLookup(A5,
europe_usedinprint, 2, False)

results in `unable to get the vlookup property of the
worksheetfunction class'

I am sure the data being sought exists in all the places it should.

any suggestions where I might be able to fix?

thanks in advance.


Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
G

Guest

You probably mean to do this:
ActiveCell.Value = Application.WorksheetFunction.VLookup(Range("A5").Value,
europe_usedinprint, 2, False)
 
L

Larry Levinson

thanks for the effort, but i get the same error.

K Dales said:
You probably mean to do this:
ActiveCell.Value = Application.WorksheetFunction.VLookup(Range("A5").Value,
europe_usedinprint, 2, False)
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
T

Tom Ogilvy

What is europe_usedinprint

is it a defined name or do you use a set statement with it such as

set europe_usedinprint = Worksheets("ABC.xls").Range("A1:F2000")

you are using it like it is the latter. If it is a defined name

ActiveCell.Value = Application.VLookup(Range("A5").Value, _
Range("europe_usedinprint"), 2, False)
 
R

Richard Buttrey

ActiveCell.Value = Application.WorksheetFunction.VLookup(A5,
europe_usedinprint, 2, False)

results in `unable to get the vlookup property of the
worksheetfunction class'

I am sure the data being sought exists in all the places it should.

any suggestions where I might be able to fix?

thanks in advance.


Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
Click to expand...

Does the vlookup work as a formula in the worksheet or do you get
#VALUE?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
L

Larry Levinson

works as a value ...

Richard Buttrey said:
Does the vlookup work as a formula in the worksheet or do you get
#VALUE?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
L

Larry Levinson

It is a defined name ... not using a set statment.


Tom Ogilvy said:
What is europe_usedinprint

is it a defined name or do you use a set statement with it such as

set europe_usedinprint = Worksheets("ABC.xls").Range("A1:F2000")

you are using it like it is the latter. If it is a defined name

ActiveCell.Value = Application.VLookup(Range("A5").Value, _
Range("europe_usedinprint"), 2, False)
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
L

Larry Levinson

error 2042 and activecell.value - #N/A


Tom Ogilvy said:
What is europe_usedinprint

is it a defined name or do you use a set statement with it such as

set europe_usedinprint = Worksheets("ABC.xls").Range("A1:F2000")

you are using it like it is the latter. If it is a defined name

ActiveCell.Value = Application.VLookup(Range("A5").Value, _
Range("europe_usedinprint"), 2, False)
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
T

Tom Ogilvy

That is the same as the #N/A that you get if you use it in a cell. It means
it didn't find the value you were looking for.

You can test it with iserror

dim res as Variant
res = Application.VLookup(Range("A5").Value, _
Range("europe_usedinprint"), 2, False)
if iserror(res) then
Msgbox "Not found"
else
activecell.value = res
End if
 
L

Larry Levinson

of course, this works as expected:

Sub bgup_foo()
Worksheets("UsedinPrint").Activate
Range("aa100").Activate

Dim res As Variant
ActiveCell.Formula = "=VLookup(A2, europe_usedinprint, 2, False)"

End Sub

I was hoping to get the vba to do it, reduce the number of formulas in
the sheet, and thereby speed up the whole process. its getting very
clunky at this point. thanks.


Tom Ogilvy said:
That is the same as the #N/A that you get if you use it in a cell. It means
it didn't find the value you were looking for.

You can test it with iserror

dim res as Variant
res = Application.VLookup(Range("A5").Value, _
Range("europe_usedinprint"), 2, False)
if iserror(res) then
Msgbox "Not found"
else
activecell.value = res
End if
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
T

Tom Ogilvy

Sub bgup_foo()
Worksheets("UsedinPrint").Activate
set rng = Range("aa100")
rng.Value = Application.VLookup(Range("A2").Value, _
Range("europe_usedinprint"), 2, False)
End Sub

Should place the same value in the cell
 
L

Larry Levinson

#N/A .. sorry.


Tom Ogilvy said:
Sub bgup_foo()
Worksheets("UsedinPrint").Activate
set rng = Range("aa100")
rng.Value = Application.VLookup(Range("A2").Value, _
Range("europe_usedinprint"), 2, False)
End Sub

Should place the same value in the cell
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
T

Tom Ogilvy

Balls in your court Larry. It is your data problem now - not a coding
problem.

I ran the bgup_foo after setting up the sheet to match and it returned the
correct answer for me.

Are you looking up a date?

If so, try

rng.Value = Application.VLookup(clng(Range("A2").Value), _
Range("europe_usedinprint"), 2, False)
 
L

Larry Levinson

ahhhhhhhhhhhh ... Yes, I am looking up dates. arrrrrggggghhhhh!
next time I ask a question, be sure to quiz me first and what I am
looking for ... checking the code ... bingo! thanks a lot ... now,
let's see if it actually speeds up matters ... cheers.



Tom Ogilvy said:
Balls in your court Larry. It is your data problem now - not a coding
problem.

I ran the bgup_foo after setting up the sheet to match and it returned the
correct answer for me.

Are you looking up a date?

If so, try

rng.Value = Application.VLookup(clng(Range("A2").Value), _
Range("europe_usedinprint"), 2, False)
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 
L

Larry Levinson

One other question: remind me how to turn off autocalculation and turn
it back on when the vba macro is at the end. I think my time problmes
are the result of recalculation of vlookup on each of, like, 11
worksheets.


Tom Ogilvy said:
Balls in your court Larry. It is your data problem now - not a coding
problem.

I ran the bgup_foo after setting up the sheet to match and it returned the
correct answer for me.

Are you looking up a date?

If so, try

rng.Value = Application.VLookup(clng(Range("A2").Value), _
Range("europe_usedinprint"), 2, False)
Click to expand...

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

excel, dates and days ... 3
activecell.formula syntax 4
conditional sum wizard 2
do a `down arrow' 1
textbox.value doesn't create a number? 2
UserForms and Variables and Types of data 6
Data Types help ... Overflow errors 4
sheet protection question 1

Top