Need help with simple syntax for entering formula via VBA

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
I'm repeating my earlier post with a different subject to see if it sparks more responses...if you're reading this, perhaps it did!

I'm sure I'm just missing something simple, but I can't seem to have VBA insert a formula to reference another cell correctly. I want to be able to have VBA put into the active cell a formula such as "=C5" so that the active cell then always references the contents of C5.

I tried:
ActiveCell.FormulaR1C1 = Range("b1").Address

But this only puts the text "$B$1" into the active cell. I then tried:

ActiveCell.FormulaR1C1 = "=" & Range("b1").Address

But this gave an "application-defined or object-defined error. I tried:

ActiveCell.FormulaR1C1 = "=" & Range("b1")

But this only put the text "=9" into the active cell (where the contents of B1 was 9), not the actual cell rerence to cell B1.

I know I'm close and missing something simple...any suggestions? Thanks!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Either: -

ActiveCell.FormulaR1C1 = "=R1C2"

or

ActiveCell.Formula = "=" & Range("B1").Address

or (not absolute)

ActiveCell.Formula = "=B1"

etc etc. Take a look at the help entry for FormulaR1C1, too.
 

Ephraim Flintwinch

New Member
Joined
Sep 8, 2002
Messages
11
On 2002-09-10 14:56, sagain2k wrote:
Thanks much for the responses! It works like a charm... Cheers,

--R

Normally the easiest way to find the syntax for entering formulas is to use the macro recorder.
 

Forum statistics

Threads
1,147,452
Messages
5,741,206
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top