# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  worksheetfunction.vlookup error

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

----------


## K Dales

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

----------


## Larry Levinson

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

"K Dales" <KDales@discussions.microsoft.com> 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 ....)

----------


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



--
Regards,
Tom Ogilvy

"Larry Levinson" <llevinsondeletethis@bloomberg.net> wrote in message
news:on47f1t1ridf052cdvqkasjavtl2oefuo8@4ax.com...
> thanks for the effort, but i get the same error.
>
> "K Dales" <KDales@discussions.microsoft.com> 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 ....)

----------


## Richard Buttrey

On Fri, 05 Aug 2005 11:52:56 -0400, Larry Levinson
<llevinsondeletethis@bloomberg.net> 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

works as a value ...

Richard Buttrey <chaos.theory.nospam.removethis@zen.co.uk> wrote:

>On Fri, 05 Aug 2005 11:52:56 -0400, Larry Levinson
><llevinsondeletethis@bloomberg.net> 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 ....)

----------


## Larry Levinson

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


"Tom Ogilvy" <twogilvy@msn.com> 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

error 2042 and activecell.value - #N/A


"Tom Ogilvy" <twogilvy@msn.com> 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 ....)

----------


## 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



"Larry Levinson" <llevinsondeletethis@bloomberg.net> wrote in message
news:l7c7f19akgdc2hk1irhmil2n4dnuk2dmtn@4ax.com...
> error 2042 and activecell.value - #N/A
>
>
> "Tom Ogilvy" <twogilvy@msn.com> 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

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" <twogilvy@msn.com> 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" <llevinsondeletethis@bloomberg.net> wrote in message
>news:l7c7f19akgdc2hk1irhmil2n4dnuk2dmtn@4ax.com...
>> error 2042 and activecell.value - #N/A
>>
>>
>> "Tom Ogilvy" <twogilvy@msn.com> 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 ....)

----------


## 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

--
Regards,
Tom Ogilvy


"Larry Levinson" <llevinsondeletethis@bloomberg.net> wrote in message
news:7cf7f1hetevip1gtsdi3dk7sj3j8q0p06b@4ax.com...
> 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" <twogilvy@msn.com> 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" <llevinsondeletethis@bloomberg.net> wrote in message
> >news:l7c7f19akgdc2hk1irhmil2n4dnuk2dmtn@4ax.com...
> >> error 2042 and activecell.value - #N/A
> >>
> >>
> >> "Tom Ogilvy" <twogilvy@msn.com> 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 ....)

----------


## Larry Levinson

#N/A .. sorry.


"Tom Ogilvy" <twogilvy@msn.com> 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 ....)

----------


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


--
Regards,
Tom Ogilvy

"Larry Levinson" <llevinsondeletethis@bloomberg.net> wrote in message
news:ish7f11rmm7tk65tj15vbmsl867hvtgcjs@4ax.com...
> #N/A .. sorry.
>
>
> "Tom Ogilvy" <twogilvy@msn.com> 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 ....)

----------


## 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" <twogilvy@msn.com> 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 ....)

----------


## 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" <twogilvy@msn.com> 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 ....)

----------


## Tom Ogilvy

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

--
Regards,
Tom Ogilvy


"Larry Levinson" <llevinsondeletethis@bloomberg.net> wrote in message
news:hns7f1dt6aib8irsbrd1andiqrs2hja9fl@4ax.com...
> 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" <twogilvy@msn.com> 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 ....)

----------

