worksheetfunction.vlookup error
 

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 ....)

worksheetfunction.vlookup error
 

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


"Larry Levinson" wrote:

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 ....)

worksheetfunction.vlookup error
 

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

"K Dales" wrote:

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

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

worksheetfunction.vlookup error
 

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)



--
Regards,
Tom Ogilvy

"Larry Levinson" wrote in message
...
thanks for the effort, but i get the same error.

"K Dales" wrote:

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

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

worksheetfunction.vlookup error
 

On Fri, 05 Aug 2005 11:52:56 -0400, Larry Levinson
wrote:

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 ....)

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

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

worksheetfunction.vlookup error
 

works as a value ...

Richard Buttrey wrote:

On Fri, 05 Aug 2005 11:52:56 -0400, Larry Levinson
wrote:

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 ....)

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

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

worksheetfunction.vlookup error
 

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


"Tom Ogilvy" wrote:

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)

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

worksheetfunction.vlookup error
 

error 2042 and activecell.value - #N/A


"Tom Ogilvy" wrote:

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)

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

worksheetfunction.vlookup error
 

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



"Larry Levinson" wrote in message
...
error 2042 and activecell.value - #N/A


"Tom Ogilvy" wrote:

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)

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

worksheetfunction.vlookup error
 

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" wrote:

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



"Larry Levinson" wrote in message
.. .
error 2042 and activecell.value - #N/A


"Tom Ogilvy" wrote:

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)

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


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

worksheetfunction.vlookup error
 

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

--
Regards,
Tom Ogilvy


"Larry Levinson" wrote in message
...
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" wrote:

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



"Larry Levinson" wrote in message
.. .
error 2042 and activecell.value - #N/A


"Tom Ogilvy" wrote:

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)

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


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

worksheetfunction.vlookup error
 

#N/A .. sorry.


"Tom Ogilvy" wrote:

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

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

worksheetfunction.vlookup error
 

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)


--
Regards,
Tom Ogilvy

"Larry Levinson" wrote in message
...
#N/A .. sorry.


"Tom Ogilvy" wrote:

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

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

worksheetfunction.vlookup error
 

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" wrote:

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)

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

worksheetfunction.vlookup error
 

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" wrote:

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)

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

worksheetfunction.vlookup error
 

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

--
Regards,
Tom Ogilvy


"Larry Levinson" wrote in message
...
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" wrote:

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)

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