Need help with simple syntax for entering formula via VBA

[sagain2k]

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!

 

[Jim North]

Didn't you like the response you got?

http://www.mrexcel.com/board/viewtopic.php?topic=21723&forum=2

 

[Mudface]

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.

 

[sagain2k]

Thanks much for the responses! It works like a charm... Cheers,

--R

 

[Ephraim Flintwinch]

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.