Help with formulas (Sum)

S

sameerce

Hi,
I wanted to insert some rows in the Excel sheet on a button clic
dynamically, and I wanted the sum of all cells of that row to b
displayed on the last cell. I tried this using Sum(B5:I5) and it work
fine for this. But, I want the same functioniality to be inserte
dynamically based on the number of row that is selected, in such a wa
the row number can be changed from 5 to 6 and so on.
I tried using:
sum((Chr(Asc("A") + 1) & iCounter + 1) : (Chr(Asc("A") + 5) & iCounte
+ 1)). But, this is not working.

I hope that I have made my problem clear enough. Can anyone suggest th
solution to this problem.

Thanks,
Samee
 
F

Frank Kabel

Hi
why not insert the formula like
cell_object.FormulaR1C1="=SUM(R[0]C2:R[0]C9)"
 
B

Bob Phillips

Hi Sameer,

This does it

Rows("10:15").EntireRow.Insert
Range("J10:J15").Formula = "=SUM(B5:I5)"

but the sum is always 0 at this point as there is no data to sum, and it is
not dynamic, that is always 10:15. it can be dynamic with

Dim StartRow As Long

StartRow = 7
Rows(StartRow & ":" & StartRow + 5).EntireRow.Insert
Range("J" & StartRow & ":J" & StartRow + 5).Formula = _
"=SUM(B" & StartRow & ":I" & StartRow & ")"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top