Macro Recording Excel 2007

A

Abdul

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
 
B

Bernard Liengme

In both XL2003 and XL2007, I get
Selection.FormulaArray = "=SUM(RC[-3])"
When I run the macros they give ={SUM(A1)}
best wishes
 
N

Niek Otten

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


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

Bernard Liengme

I have done it again in a new workbook and still get {=SUM(A1)} in XL 2007
best wishes
 
J

JLGWhiz

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

Andy Pope

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


| 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
|
| | > 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
| >
|
|
Click to expand...
Click to expand...
 
B

Bernard Liengme

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


| 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
|
| | > 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
| >
|
|
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Recording 2007 1
watch macro while it is recording 0
sum a column with a variable 6
Speeding Up Macro in VBA 4
macro recording 3
userform exception error 4 textboxes 7
VBA Pivot Table Error 1
Recording a Macro 3

Top