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

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

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
|
|
|

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
|
|
|

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

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
|
|
|

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
|
|
|