Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
This is from an earlier post
Start in cell D1 or somewhere in row 1 Record this: Array-enter =SUM($A1) What gets recorded is Selection.FormulaArray = "=SUM(RC1)" Play it back. What gets entered is the array-entered =SUM(RC1) INTERESTING! Do any one have a solution ? probably any big fix in Excel 2007? Thanks, Abdul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
In both XL2003 and XL2007, I get
Selection.FormulaArray = "=SUM(RC[-3])" When I run the macros they give ={SUM(A1)} best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Abdul" wrote in message oups.com... This is from an earlier post Start in cell D1 or somewhere in row 1 Record this: Array-enter =SUM($A1) What gets recorded is Selection.FormulaArray = "=SUM(RC1)" Play it back. What gets entered is the array-entered =SUM(RC1) INTERESTING! Do any one have a solution ? probably any big fix in Excel 2007? Thanks, Abdul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
In Excel 2007, I get
{=SUM(RC1)} In Excel 2003, I get {=SUM($A1)} Of course in Excel2003 RC1 could not be mistaken for a cell address, but still this seems to be wrong -- Kind regards, Niek Otten Microsoft MVP - Excel "Bernard Liengme" wrote in message ... | In both XL2003 and XL2007, I get | Selection.FormulaArray = "=SUM(RC[-3])" | When I run the macros they give ={SUM(A1)} | best wishes | -- | Bernard V Liengme | Microsoft Excel MVP | www.stfx.ca/people/bliengme | remove caps from email | | "Abdul" wrote in message | oups.com... | This is from an earlier post | | Start in cell D1 or somewhere in row 1 | Record this: | Array-enter =SUM($A1) | What gets recorded is | Selection.FormulaArray = "=SUM(RC1)" | Play it back. | What gets entered is the array-entered | =SUM(RC1) | INTERESTING! | | Do any one have a solution ? probably any big fix in Excel 2007? | | Thanks, | | Abdul | | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
I have done it again in a new workbook and still get {=SUM(A1)} in XL 2007
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Niek Otten" wrote in message ... In Excel 2007, I get {=SUM(RC1)} In Excel 2003, I get {=SUM($A1)} Of course in Excel2003 RC1 could not be mistaken for a cell address, but still this seems to be wrong -- Kind regards, Niek Otten Microsoft MVP - Excel "Bernard Liengme" wrote in message ... | In both XL2003 and XL2007, I get | Selection.FormulaArray = "=SUM(RC[-3])" | When I run the macros they give ={SUM(A1)} | best wishes | -- | Bernard V Liengme | Microsoft Excel MVP | www.stfx.ca/people/bliengme | remove caps from email | | "Abdul" wrote in message | oups.com... | This is from an earlier post | | Start in cell D1 or somewhere in row 1 | Record this: | Array-enter =SUM($A1) | What gets recorded is | Selection.FormulaArray = "=SUM(RC1)" | Play it back. | What gets entered is the array-entered | =SUM(RC1) | INTERESTING! | | Do any one have a solution ? probably any big fix in Excel 2007? | | Thanks, | | Abdul | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
The difference between the recorded entry of Sum(R[C-3]) and Sum(RC1) is
whether you use the $ to make absolute reference or omit the $ and make relative reference. They both yield {Sum(A1)} as the array formula in the entry cell. "Abdul" wrote: This is from an earlier post Start in cell D1 or somewhere in row 1 Record this: Array-enter =SUM($A1) What gets recorded is Selection.FormulaArray = "=SUM(RC1)" Play it back. What gets entered is the array-entered =SUM(RC1) INTERESTING! Do any one have a solution ? probably any big fix in Excel 2007? Thanks, Abdul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
Hi Bernard,
I had trouble reproducing the problem until I used a formula that referenced the same row as where I was entering the formula. And remember to include the absolute reference to the column, The problem is there in a plain SUM formula also. So in D1 the formula =SUM($A1) recorded syntax is ActiveCell.FormulaR1C1 = "=SUM(RC1)" The recorder should really produce a zero row reference in order to avoid cell name clashes. ActiveCell.FormulaR1C1 = "=SUM(R[0]C1)" Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bernard Liengme" wrote in message ... I have done it again in a new workbook and still get {=SUM(A1)} in XL 2007 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Niek Otten" wrote in message ... In Excel 2007, I get {=SUM(RC1)} In Excel 2003, I get {=SUM($A1)} Of course in Excel2003 RC1 could not be mistaken for a cell address, but still this seems to be wrong -- Kind regards, Niek Otten Microsoft MVP - Excel "Bernard Liengme" wrote in message ... | In both XL2003 and XL2007, I get | Selection.FormulaArray = "=SUM(RC[-3])" | When I run the macros they give ={SUM(A1)} | best wishes | -- | Bernard V Liengme | Microsoft Excel MVP | www.stfx.ca/people/bliengme | remove caps from email | | "Abdul" wrote in message | oups.com... | This is from an earlier post | | Start in cell D1 or somewhere in row 1 | Record this: | Array-enter =SUM($A1) | What gets recorded is | Selection.FormulaArray = "=SUM(RC1)" | Play it back. | What gets entered is the array-entered | =SUM(RC1) | INTERESTING! | | Do any one have a solution ? probably any big fix in Excel 2007? | | Thanks, | | Abdul | | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Recording Excel 2007
Thanks for the clarification, Andy. Yes, now I get
ActiveCell.FormulaR1C1 = "=SUM(RC1)" when I include the $ in =SUM($A1) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Andy Pope" wrote in message ... Hi Bernard, I had trouble reproducing the problem until I used a formula that referenced the same row as where I was entering the formula. And remember to include the absolute reference to the column, The problem is there in a plain SUM formula also. So in D1 the formula =SUM($A1) recorded syntax is ActiveCell.FormulaR1C1 = "=SUM(RC1)" The recorder should really produce a zero row reference in order to avoid cell name clashes. ActiveCell.FormulaR1C1 = "=SUM(R[0]C1)" Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bernard Liengme" wrote in message ... I have done it again in a new workbook and still get {=SUM(A1)} in XL 2007 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Niek Otten" wrote in message ... In Excel 2007, I get {=SUM(RC1)} In Excel 2003, I get {=SUM($A1)} Of course in Excel2003 RC1 could not be mistaken for a cell address, but still this seems to be wrong -- Kind regards, Niek Otten Microsoft MVP - Excel "Bernard Liengme" wrote in message ... | In both XL2003 and XL2007, I get | Selection.FormulaArray = "=SUM(RC[-3])" | When I run the macros they give ={SUM(A1)} | best wishes | -- | Bernard V Liengme | Microsoft Excel MVP | www.stfx.ca/people/bliengme | remove caps from email | | "Abdul" wrote in message | oups.com... | This is from an earlier post | | Start in cell D1 or somewhere in row 1 | Record this: | Array-enter =SUM($A1) | What gets recorded is | Selection.FormulaArray = "=SUM(RC1)" | Play it back. | What gets entered is the array-entered | =SUM(RC1) | INTERESTING! | | Do any one have a solution ? probably any big fix in Excel 2007? | | Thanks, | | Abdul | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 recording macro | Excel Discussion (Misc queries) | |||
Macro Recording 2007 | Excel Programming | |||
Excel 2007 macro recording: objects | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
2007: Macro recording not working for some actions | Excel Programming |