PDA

View Full Version : Macro with sum formula



udigold1
02-18-2009, 07:09 AM
Hi,

I have this code:



Sub sikumMhM()
r1 = Range("outputMhm").End(xlDown).Row + 1
r2 = Range("outputMhm").Row + 1
c1 = Range("mhm").Column
c2 = c1 + Range("mhm").Columns.Count - 2
c3 = Range("outputMhm").Column
c4 = col - 2

Range(Cells(r1, c3), Cells(r1, c2)).Select
Selection.Font.Size = 13
Selection.Borders(xlEdgeTop).Weight = xlThick

Cells(r1, c3) = "озо"
For col = c1 To c2
If Cells(Range("MHM").Row, col) = "MHM" Then Cells(r1, col).FormulaR1C1
= "=SUM(R" & r2 & "C:R[-1]C)/sum(R" & r2 & "C" & col - 2 & ":R[-1]C[-2])"
Next col

End Sub


It works fine except for the fact the I get the dollar sign for the first row, for example : sum(R$8:R48)/sum($D$8:D48)

How do I get rid of the $ sign?

Thanks,
Udi

Bob Phillips
02-18-2009, 09:20 AM
Is this what you want



For col = c1 To c2
If Cells(Range("MHM").Row, col) = "MHM" Then Cells(r1, col).FormulaR1C1 = _
"=SUM(R[" & r2 & "]C:R[-1]C)/sum(R" & r2 & "C" & col - 2 & ":R[-1]C[-2])"
Next col