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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0

Forum statistics

Threads
1,218,794
Messages
6,144,518
Members
450,550
Latest member
Melanie_Annies

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
Back
Top