Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
How do I create a Macro that creates a formula in a cell instead of just inputting a value? Suppose I want to add two cells: 1.) C:\Documents and Settings\pgalla06\Desktop\Company\[Wolf.xls]Other Coverages'!$G$7 and 2.) Y4 So that the formula in the cell reads: =C:\Documents and Settings\pgalla06\Desktop\Company\[Wolf.xls]Other Coverages'!$G$7+Y4 I only know how to create macro that inputs a value in the cell not a formula. Any help is greatly appreciated. Thanks, PEter -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=379509 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Just put quotes around your formula as you would put it directly into worksheet cell. Range("A1") = "=C:\Documents an Settings\pgalla06\Desktop\Company\[Wolf.xls]Other Coverages'!$G$7+Y4" Substitute the destination cell for A1 in the range assignment HT -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=37950 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
bhofsetz, Thanks for getting back to me I can't seem to get that to work. I don't want to hardcode the link to the outside worksheet because this macro will be used with different spreadsheets. So I tried to create a variable that would call the cell outside the worksheet where the macro sits. I don't know if I explained my problem very well so here is a copy of part of the macro that I'm working on. Application.Workbooks(DataEntryVar).Worksheets("Ca sualty Income").Activate Dummy1 = Range("G8").Address Windows("Stewardship MACRO.xls").Activate Dummy2 = Range("D2").Address MajorClass = "Casualty Income" Temp = Path & "[" & Company & "]" & MajorClass & "!'" & Dummy1 Range("C2").Formula = D2 + Temp -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=379509 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
PGalla06, Check your syntax carefully. It looks like you are missing a single quote ' before the ! in your formula. Also you may want to use the Watch window to keep an eye on all your variables. Path, Company, Major Class, Dummy1 and Dummy2 Step through the code and make sure they are what you expect. Also, I didn't see anywhere that you assigned Path and if you activate the "Stewardship MACRO.xls" workbook then the Path will be different than when the "DataEntryVar.xls" workbook is activated. Hope this Helps -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379509 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Actually it looks like you DO have the single quote ' but just on th wrong side of the ! What kind of error are you getting -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=37950 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Use sheet references if the cell(s) is on another worksheet.
Use workbook references if the cell is in another workbook To get the hang of it - record a macro while you build the formula in your workbook. -- steveB Remove "AYN" from email to respond "bhofsetz" wrote in message ... Actually it looks like you DO have the single quote ' but just on the wrong side of the ! What kind of error are you getting? -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379509 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
I got it to work! It turns out i was missing an ' at the beginning to the address and I think I was putting spaces where I didnt need them. That said, do you know of an easier way to write this macro. Thanks, Peter Application.Workbooks(DataEntryVar).Worksheets("Ca sualty Income").Activate Dummy1 = Range("G8").Address Windows("Stewardship MACRO.xls").Activate Dummy2 = Range("D2").Address MajorClass = "Casualty Income" Te = "'" & Path & "[" & Company & "]" & MajorClass & "'!" & Dummy1 Range("C2").Formula = "=D2 +" & Te -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=379509 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
You could avoid some of the Activating that you have going on by assigning your Dummy1 variable directly replace: Application.Workbooks(DataEntryVar).Worksheets("Ca sualty Income").Activate Dummy1 = Range("G8").Address with: Dummy1 = Application.Workbooks(DataEntryVar).Worksheets("Ca sualty Income").Range("G8").Address Also, if your destination cell is always D2 then you don't need Dummy2 (you aren't using it right now anyway) And if the macro is running from the "Stewardship MACRO.xls" then you can simplify things by using: ThisWorkbook.Range("C2").Formula = "=D2" & "+" & Te This is untested but give it a try: Code: -------------------- Dummy1 = Application.Workbooks(DataEntryVar).Worksheets("Ca sualty Income").Range("G8").Address MajorClass = "Casualty Income" Te = "'" & Path & "[" & Company & "]" & MajorClass & "'!" & Dummy1 ThisWorkbook.Range("C2").Formula = "=D2" & "+" & Te -------------------- HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379509 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions |