Macro help - formula showing #DIV/0!

  • Thread starter Thread starter Kenneth E. Coakley
  • Start date Start date
K

Kenneth E. Coakley

I've created a macro that inserts a formula in my current cell and then
formats the result. The macro looks like this:

ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(A:A)"
ActiveCell.Select
Selection.NumberFormat = "0.0%"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


Upon executing the macro, a "#DIV/0!" results even though the included
columns are properly filled with numeric values. In fact, if I simply select
the cell, put my insertion point in the formula bar and then click the green
check mark, the correct value appears even though nothing has changed.

What am I doing wrong?

Thanks for any help!
Ken
 
Hi
try changing the line
ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(A:A)"

to
ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(C1:C1)"
 
You're mixing R1C1 and A1-style notations. Try:

ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(C1)"
 
Thanks for the prompt reply! It seems so obvious now that it's been pointed
out, but mixing the notations is a no-no.

Thanks again!

Ken


JE McGimpsey said:
You're mixing R1C1 and A1-style notations. Try:

ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(C1)"

Kenneth E. Coakley said:
I've created a macro that inserts a formula in my current cell and then
formats the result. The macro looks like this:

ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(A:A)"
ActiveCell.Select
Selection.NumberFormat = "0.0%"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


Upon executing the macro, a "#DIV/0!" results even though the included
columns are properly filled with numeric values. In fact, if I simply select
the cell, put my insertion point in the formula bar and then click the green
check mark, the correct value appears even though nothing has changed.

What am I doing wrong?

Thanks for any help!
Ken
Click to expand...
Click to expand...
 
Thanks for the prompt reply! It seems so obvious now that it's been pointed
out, but mixing the notations is a no-no.

Thanks again!

Ken


Frank Kabel said:
Hi
try changing the line
ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(A:A)"

to
ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(C1:C1)"


--
Regards
Frank Kabel
Frankfurt, Germany

Kenneth E. Coakley said:
I've created a macro that inserts a formula in my current cell and then
formats the result. The macro looks like this:

ActiveCell.FormulaR1C1 = "=COUNT(C[1])/COUNT(A:A)"
ActiveCell.Select
Selection.NumberFormat = "0.0%"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


Upon executing the macro, a "#DIV/0!" results even though the included
columns are properly filled with numeric values. In fact, if I simply select
the cell, put my insertion point in the formula bar and then click the green
check mark, the correct value appears even though nothing has changed.

What am I doing wrong?

Thanks for any help!
Ken
Click to expand...
Click to expand...
 
Back
Top