VBA syntax to insert a formula to link to another worksheet cell

[ebrandt]

trying to insert a formula to link a cell to another worksheet in my workbook, which I I’m have assigned a text variable “Sheet_Name”.

This is the code I have written………………..


Range("F13").Select

ActiveCell.FormulaR1C1 = "=’" & Sheet_Name & "'!D13"



This is the result I get which produces an error……………………………..

=@'03.00 - Building Concrete'!'D13'

What am I doing wrong?

 

[Joe4]

It looks like you have a slanted quote mark in your formula ’ instead of the straight up and down one of '
Excel does not like slanted ones.

Also, no need to select a cell before applying a formula to it, and remove the R1C1 reference, as you are not using relative ranges in your formula.
Try:

Range("F13").Formula = "='" & Sheet_Name & "'!D13"

 

[ebrandt]

It looks like you have a slanted quote mark in your formula ’ instead of the straight up and down one of '
Excel does not like slanted ones.

Also, no need to select a cell before applying a formula to it, and remove the R1C1 reference, as you are not using relative ranges in your formula.
Try:

Range("F13").Formula = "='" & Sheet_Name & "'!D13"

That worked.

Why doesn't this...............

Range("H13").Formula = "=IF(" & Sheet_Name & "!F13>1," & Sheet_Name & "!F13,x)"

 

[Joe4]

That worked.

Why doesn't this...............

Range("H13").Formula = "=IF(" & Sheet_Name & "!F13>1," & Sheet_Name & "!F13,x)"

What is the value of Sheet_Name?
And what is the "x" at the end of your equation?
What is supposed to be happening there?

Here is a little hint. If you know what the formula is supposed to look like on the sheet (for just one case/example), turn on your Macro Recorder, and record yourself entering it into the sheet. Then view the code and see what it looks like.

Then, you can check to see if what you are building matches that with a simple Message Box, i.e. take the formula from here:

Range("H13").Formula = "=IF(" & Sheet_Name & "!F13>1," & Sheet_Name & "!F13,x)"

and return it via a Message Box:

MsgBox "=IF(" & Sheet_Name & "!F13>1," & Sheet_Name & "!F13,x)"

 

[ebrandt]

Thanks for your help! Got it working.

 

[Joe4]

Thanks for your help! Got it working.

You are welcome!