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

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

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

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

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

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

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

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