VLOOKP

[Roy Kirkland]

I am writing an Excel application with considerable VBA in the back end - I
have been attempting to use data stored on one sheet to supply variables for
a calculation on another sheet, but have not been able to get Vlookup to
work across sheets. Is there a workaround for this, or does anyone know the
code behind the function, and if so, is it possible to write this code as a
user defined function?

Roy

 

[Bob Phillips]

Hi Roy,

It works fine

=VLOOKUP(A1,Sheet2!A1:A100,2,FALSE)

vut why use VLOOKUP in VBA?

 

[Frank Kabel]

Hi
see your post in excel.programming

 

[JulieD]

Hi Roy

VLOOKUP generally works fine across worksheets
e.g.
=VLOOKUP(A1,Sheet2!$A$1:$B$10,2,0)
where A1 is on sheet1

if you'ld like to paste your exact formula we'll be happy to look at it for
you

Cheers
JulieD

 

[Myrna Larson]

Just curious, but why *not*?

With a sorted array, it used to be slower than a VBA binary search, but in
recent versions that seems not to be true.

 

[Bob Phillips]

Hi Myrna,

I just try to avoid worksheet functions in VB code, as I try to be
application independent where I can (I know, rarely possible, but I try). I
would only use ws functions to do things that I can't do in VB, and usually
(often?) the VB requirement doesn't need the full ws function
functionality.

 

[Myrna Larson]

I understand. Since I don't do any macros to use anywhere except the program
in which I develop them (Excel, mostly, a little Access and a little Word), I
don't feel a need to make them application-independent.

 

[Roy Kirkland]

Thanks group - I think I was not getting results because the book I was
using for the syntax did not have an adequate example. Can you believe it,
not a single example. I'm trying to use the worksheet function because of
the way I have to loop the application etc. - Makes for less testing.
Thanks again.

Roy