How to define cell value for use in vlookup formula

[ThrottleWorks]

I want to define cell value for use in vlookup

For original formula is =vlookup(n2,N,2,0)


I want to replace range n2 with myval

I have declared myval as


Dim myval as string

Myval = activecell.value


Then I run the next part which is

=vlookup(myisn,n,2,0)

But this is giving me error.


Can someone help me in this please.

 

[Jai9]

Hi,


Shouldn't it be myval instead of myisn?


Try,


= Application.WorksheetFunction.VLookup(myval, Range("N:O"), 2, 0)


Jai

 

[ThrottleWorks]

Hi Jai,


it is working, thanks a lot for the help.


Have a nice day.


Sachin.

 

[kaushik03]

Hi sachinbizboy,


I do not understand what you are after exactly.


Are you defining this variable (to store the lookup value) in vba module and passing the same when you write the formula in worksheet? Or everything you are doing through VBA only?Again I do not understand what is myisn here?


Both the cases you would get errors only.


If you are doing it through VBA then the format of Vlookup that you are using is not correct.On other case (passing the variable in worksheet function) would definitely throw you #name error.


Again I do not understand the requirement of storing the lookup value by activecell.value statement.It looks like your lookup value would be the one where your cursor is placed.If you could explain this, it would be great.


However, in sheet1 of below file (check the module1), if you click the button, cursor will move to E1(lookup value cell), vlookup will be performed and you would get the result at F1.

http://speedy.sh/Zsm98/Vlookup-macro.xlsm


In case I fail to give you the exact solution, please consider uploading a sample file / explain your problem bit more clearly.


Kaushik