Insert Formula

[Guest]

Hi

I've been trying to get this to work. I want to programmatically insert
this formula into a cell in the active worksheet. I want to link the two
cells which are in the same workbook.

='Main Sheet'!$E$9

I've tried various ways with no luck. I just don't seem to get this.

Here's my latest try.

With ActiveSheet
..Range("G9").Formula = "'=Main Sheet'!$E$9"
End With

The formula is there but as a string.

Thanks

 

[Guest]

Hi Karen,

Try this:-

With ActiveSheet
.Range("G9").FormulaR1C1 = "='Main Sheet'!RC[-2]"
End With

As an added lesson, try recording the macro to obtain the syntax when you
are having problems. It would have recorded like this:-

Range("G9").Select
ActiveCell.FormulaR1C1 = "='Main Sheet'!RC[-2]"

then you can edit it to get the above.

Regards,

OssieMac

 

[Guest]

Just another little snippet. If you recorded the macro so that the link
address is absolute (that is press F4 immediately after selecting the the
linked cell) then it would look like this:-

ActiveCell.FormulaR1C1 = "='Main Sheet'!R9C5"

You will see that although VBA uses Row Column for the cell address, when
you view the formula on the work sheet it is ='Main Sheet'!$E$9

regards,

OssieMac

Hi Karen,

Try this:-

With ActiveSheet
.Range("G9").FormulaR1C1 = "='Main Sheet'!RC[-2]"
End With

As an added lesson, try recording the macro to obtain the syntax when you
are having problems. It would have recorded like this:-

Range("G9").Select
ActiveCell.FormulaR1C1 = "='Main Sheet'!RC[-2]"

then you can edit it to get the above.

Regards,

OssieMac

Hi

I've been trying to get this to work. I want to programmatically insert
this formula into a cell in the active worksheet. I want to link the two
cells which are in the same workbook.

='Main Sheet'!$E$9

I've tried various ways with no luck. I just don't seem to get this.

Here's my latest try.

With ActiveSheet
.Range("G9").Formula = "'=Main Sheet'!$E$9"
End With

The formula is there but as a string.

Thanks

 

[Guest]

Thank you OssieMac!

After so much frustration it's heartening to see I was so close to the
correct answer!

Karen

Just another little snippet. If you recorded the macro so that the link
address is absolute (that is press F4 immediately after selecting the the
linked cell) then it would look like this:-

ActiveCell.FormulaR1C1 = "='Main Sheet'!R9C5"

You will see that although VBA uses Row Column for the cell address, when
you view the formula on the work sheet it is ='Main Sheet'!$E$9

regards,

OssieMac

Hi Karen,

Try this:-

With ActiveSheet
.Range("G9").FormulaR1C1 = "='Main Sheet'!RC[-2]"
End With

As an added lesson, try recording the macro to obtain the syntax when you
are having problems. It would have recorded like this:-

Range("G9").Select
ActiveCell.FormulaR1C1 = "='Main Sheet'!RC[-2]"

then you can edit it to get the above.

Regards,

OssieMac

Hi

I've been trying to get this to work. I want to programmatically insert
this formula into a cell in the active worksheet. I want to link the two
cells which are in the same workbook.

='Main Sheet'!$E$9

I've tried various ways with no luck. I just don't seem to get this.

Here's my latest try.

With ActiveSheet
.Range("G9").Formula = "'=Main Sheet'!$E$9"
End With

The formula is there but as a string.

Thanks