Storing result of function directly in a variable instead of cell.

[Mak]

When I create code excel macros in VBA I use excel functions and I have to
store them explicitly in some cell.


for eg:

dim a as integer
cells(5,6).formula = "=vlookup(A1, B50:c60,2,0)" 'correct

a = "=vlookup(A1, B50:c60,2,0)" 'incorrect--> i need to do this thing so
that I dont have to store the formula explicitly in a cell.

plz help in this regard. I would be highly obliged.

Regards,
Mak

 

[Jon Peltier]

Learn to use the Object Browser. In the VB Editor (VBE), go to View menu >
Object Browser. Scroll down in the left side panel to WorksheetFunction,
then scroll in the right to VLookup. It shows this syntax, which looks very
familiar, eh?

Function VLookup(Arg1, Arg2, Arg3, [Arg4])

Use the appropriate VBA syntax for the arguments:

a = worksheetfunction.vlookup(activesheet.range("A1"),
activesheet.range("B50:C60"), 2, 0)

You should have the Object Browser open whenever you're working in the VBE.
It is a source of so much assistance, you will wonder how you did anything
without it.

- Jon

 

[Gary''s Student]

a=Evaluate("=vlookup(A1, B50:c60,2,0)")

 

[Dave Peterson]

Dim Res as Variant 'could be an error
dim myRng as range
dim myVal as range

with worksheets("somesheetnamehere")
set myrng = .range("b50:c60)
myval = .range("a1").value
end with

res = application.vlookup(myval, myRng, 2, 0)

if iserror(res) then
'what should happen with an error?
else
msgbox res
end if

 

[Dave Peterson]

Corrected a typo:

Dim Res as Variant 'could be an error
dim myRng as range
dim myVal as range

with worksheets("somesheetnamehere")
set myrng = .range("b50:c60") '<-- added final double quote
myval = .range("a1").value
end with

res = application.vlookup(myval, myRng, 2, 0)

if iserror(res) then
'what should happen with an error?
else
msgbox res
end if