I want to use VBA to inert a formula into a cell.

I want to get the contents of Cell B7 on the "Balance Sheet" and insert that
into cell B$ on the current sheet.
Here is what I have but it's not working:
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Balance Sheet'!B7"

When I do that I get a #NAME? error.

Help please!

And... thanks!

Jon

since your formula is written in A1 style referencing, why would you use
formulaR1C1 telling excel it is in R1C1 style?

ActiveCell.Formula = "='Balance Sheet'!B7"

should work.

--
Regards,
Tom Ogilvy


"jonco" wrote:

I want to use VBA to inert a formula into a cell.

I want to get the contents of Cell B7 on the "Balance Sheet" and insert that
into cell B$ on the current sheet.
Here is what I have but it's not working:
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Balance Sheet'!B7"

When I do that I get a #NAME? error.

Help please!

And... thanks!

Jon

The problem is you are trying to use A1 style referencing, rather than R1C1
style. You can change your code to work as:

Range("B4").Select
ActiveCell.FormulaR1C1 = "='Balance Sheet'!R7C2"

Or, you can simply use:

Range("B4") = Sheets("Balance Sheet").Range("B7")

The above will not enter a formula, so if you do in fact need a formula, you
can use the R1C1 style as:

Range("B4").FormulaR1C1 = "='Balance Sheet'!R7C2"

Jason

"jonco" wrote:

I want to use VBA to inert a formula into a cell.

I want to get the contents of Cell B7 on the "Balance Sheet" and insert that
into cell B$ on the current sheet.
Here is what I have but it's not working:
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Balance Sheet'!B7"

When I do that I get a #NAME? error.

Help please!

And... thanks!

Jon

I used that (R1C1) because I found it somewhere else and it seemed to work
for that particular application.
Still struggling with the syntax. But your ideas both worked.

I DO appreciate the help!

Thanks

"jonco" wrote in message
. net...
I want to use VBA to inert a formula into a cell.

I want to get the contents of Cell B7 on the "Balance Sheet" and insert
that into cell B$ on the current sheet.
Here is what I have but it's not working:
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Balance Sheet'!B7"

When I do that I get a #NAME? error.

Help please!

And... thanks!

Jon