Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
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 "Karen53" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
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 "OssieMac" wrote: 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 "Karen53" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
Thank you OssieMac!
After so much frustration it's heartening to see I was so close to the correct answer! Karen "OssieMac" wrote: 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 "OssieMac" wrote: 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 "Karen53" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert formula | Excel Programming | |||
trying to insert a formula | Excel Programming | |||
How to insert = in formula bar | Excel Discussion (Misc queries) | |||
insert a new row by formula | Excel Worksheet Functions | |||
Insert Formula | Excel Programming |