Help with formulas (Sum)
 

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

--
Message posted from http://www.ExcelForum.com

Help with formulas (Sum)
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Hi,
I wanted to insert some rows in the Excel sheet on a button click
dynamically, and I wanted the sum of all cells of that row to be
displayed on the last cell. I tried this using Sum(B5:I5) and it
works
fine for this. But, I want the same functioniality to be inserted
dynamically based on the number of row that is selected, in such a
way
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) &
iCounter
+ 1)). But, this is not working.

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

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/

Help with formulas (Sum)
 

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)

"sameerce " wrote in message
...
Hi,
I wanted to insert some rows in the Excel sheet on a button click
dynamically, and I wanted the sum of all cells of that row to be
displayed on the last cell. I tried this using Sum(B5:I5) and it works
fine for this. But, I want the same functioniality to be inserted
dynamically based on the number of row that is selected, in such a way
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) & iCounter
+ 1)). But, this is not working.

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

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/

Help with formulas (Sum)
 

The code send by you works fine. Thanks a lot.
Samee

--
Message posted from http://www.ExcelForum.com