New macro question..?

[Guest]

Hello all,

Is there any way that you can change the format of a macro to show the A1
format from the R1C1 format as my macro listing shows it as R1C1 and I cannot
make head nor tail of it. The macro was a recording and I am now editing it
but the following code is as follows :

ActiveCell.FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"

but I would like to be shown as :

ActiveCell.Formula = "=SUM(d6:d33)

Regards,

 

[Bob Phillips]

Just type the RC formula in the immediate window, then go to Excel and see
what the cell shows.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

So use that
ActiveCell.Formula = "=SUM(d6:d33)"

or to just leave the value instead of the formula
Sub sumrange_value()
'ActiveCell.Value = Application.Sum(Range("d6:d33"))

' for the activecell.column

ac = ActiveCell.Column
ActiveCell.Value = Application.Sum(Range(Cells(6, ac), Cells(33, ac)))

End Sub

 

[Guest]

Hi there,

Thanks for the replies but maybe I wasn't clear about what I was asking. I
recorded a macro to change the format of the spreadsheet and add in formalae
to calculate the columns. When hitting ALT+F11 and edit the code I noticed
that the formulae is formatted as I had said previously. And I was wondering
was this a default for the VB Code to do this rather than use the A1 format
and if so how do I change it so that it does not use the R1C1 format.
--
Niall

So use that
ActiveCell.Formula = "=SUM(d6:d33)"

or to just leave the value instead of the formula
Sub sumrange_value()
'ActiveCell.Value = Application.Sum(Range("d6:d33"))

' for the activecell.column

ac = ActiveCell.Column
ActiveCell.Value = Application.Sum(Range(Cells(6, ac), Cells(33, ac)))

End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello all,

Is there any way that you can change the format of a macro to show the A1
format from the R1C1 format as my macro listing shows it as R1C1 and I
cannot
make head nor tail of it. The macro was a recording and I am now editing
it
but the following code is as follows :

ActiveCell.FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"

but I would like to be shown as :

ActiveCell.Formula = "=SUM(d6:d33)

Regards,

 

[Bob Phillips]

In record macro, you can't.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi there,

Thanks for the replies but maybe I wasn't clear about what I was asking.
I
recorded a macro to change the format of the spreadsheet and add in
formalae
to calculate the columns. When hitting ALT+F11 and edit the code I
noticed
that the formulae is formatted as I had said previously. And I was
wondering
was this a default for the VB Code to do this rather than use the A1
format
and if so how do I change it so that it does not use the R1C1 format.
--
Niall

So use that
ActiveCell.Formula = "=SUM(d6:d33)"

or to just leave the value instead of the formula
Sub sumrange_value()
'ActiveCell.Value = Application.Sum(Range("d6:d33"))

' for the activecell.column

ac = ActiveCell.Column
ActiveCell.Value = Application.Sum(Range(Cells(6, ac), Cells(33, ac)))

End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello all,

Is there any way that you can change the format of a macro to show the
A1
format from the R1C1 format as my macro listing shows it as R1C1 and I
cannot
make head nor tail of it. The macro was a recording and I am now
editing
it
but the following code is as follows :

ActiveCell.FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"

but I would like to be shown as :

ActiveCell.Formula = "=SUM(d6:d33)

Regards,