Vlookup to VBA

[Gemz]

I posted the below on the excel worksheet function forum and was advised to
post here: please see two queries below.

Thanks.

1)
I have the following formula:

=VLOOKUP(B5,INDIRECT("'"&INDEX(WSlist,MATCH(1,--(COUNTIF(INDIRECT("'"&WSlist&"'!E:E"),B5)>0),0))&"'!E:E"),1,0)

It works fine but i was wondering if there is a way to get it to work as
part of a macro? If i leave it as a formula then this means that the same
sheet will have to be copied across into each new months file, i would like a
button to just do the job
instead - find the info required and put it in a new sheet that it will
automatically
be re-named "lookup". is this possible?

2)
i am looking to do another type of lookup -i have 3 different sources of
information and i have to do 3 different lookups between each source to find
out which staff member is on each:

whose on source 1 and not on source 2
whose on source 2 and not on source 1
who is on source 1 that is also on source 3

is there a quicker way around this i macro? or would it jus be 3 different
lookups?

thanks a lot for your help, really appreciate it.

 

[SteveM]

I posted the below on the excel worksheet function forum and was advised to
post here: please see two queries below.

Thanks.

1)
I have the following formula:

=VLOOKUP(B5,INDIRECT("'"&INDEX(WSlist,MATCH(1,--(COUNTIF(INDIRECT("'"&WSlist&"'!E:E"),B5)>0),0))&"'!E:E"),1,0)

It works fine but i was wondering if there is a way to get it to work as
part of a macro? If i leave it as a formula then this means that the same
sheet will have to be copied across into each new months file, i would like a
button to just do the job
instead - find the info required and put it in a new sheet that it will
automatically
be re-named "lookup". is this possible?

2)
i am looking to do another type of lookup -i have 3 different sources of
information and i have to do 3 different lookups between each source to find
out which staff member is on each:


is there a quicker way around this i macro? or would it jus be 3 different
lookups?

thanks a lot for your help, really appreciate it.

Gemz,

Yeah, you can do it in VBA using the WorksheetFunction method. It
starts off like this:

someVariable = WorkSheetFunction.("VLookUp(...

And then you have to fill in all the parameters to complete the
statement. Embedding multiple WorksheetFunctions into a single VB
statement can be a bear, so it's better to calculate intermediate
values in separate VB statements using the results from one as the
parameters for the next.

To use the marco the way you intend really requires you to create a VB
function rather as sub i.e.

Public Function NewFunction(parameter1, parameter2, ...) as Variable
Type

Your code here...

End Function

And pass your parameters to the function and have the function return
the calculated value back to the target cell.

When you create a user defined function, it will appear in the
function list and selecting it will reveal a standard function dialog
box with an entry box for each parameter that you defined. Ranges are
accepted too like with regular functions.

If you want to know more, suggest you hunt around the net for advice
on building functions. But they are pretty easy to work with.

Good Luck,

SteveM

 

[Gemz]

thanks for the response, unfortunately i do not understand much of what you
said so am unable to really do anything with it.. i am a novice user please
can you not start me off with a code? i dont understand: " it's better to
calculate intermediate values in separate VB statements using the results
from one as the
parameters for the next."

can i not jus put all my code in 1 line and then attach that macro to a
button so that when my colleague needs to use this on a monthly basis he can
jus press the button?

i have seen previous posts relating to codes in VB but am unable to relate
to mine.

really appreciate your help.

thanks.