Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing result of function directly in a variable instead of cell.
a=Evaluate("=vlookup(A1, B50:c60,2,0)")
-- Gary''s Student - gsnu200772 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing a range variable in a cell | Excel Discussion (Misc queries) | |||
Show formulas result + text directly | Excel Worksheet Functions | |||
Variable as result of Divide (Sum) Function | Excel Programming | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
Storing value in a variable from a cell | Excel Programming |