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

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

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mak" wrote in message
...
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

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

a=Evaluate("=vlookup(A1, B50:c60,2,0)")
--
Gary''s Student - gsnu200772

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

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



Mak wrote:

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



--

Dave Peterson

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

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

Dave Peterson wrote:

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

Mak wrote:

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



--

Dave Peterson

--

Dave Peterson