Excel 'Sum' Question

[AgentM]

I have this question in another thread " What give Sum of user selected cellrange on sheet"

Here's my question again:-
What's the code for calculating a range of values in an excel column using VB.
I am trying to get the 'Sum' in excel to work.

here's a part of my code.
Dim CCExcelApp
Set ExcelApp = CreateObject("Excel.application&quot
With ExcelApp
.workbooks.open "filename.xls"
row = 7
For i = 1 To 20
.range("A" & CStr(row)).Select
.activecell.formular1c1 = i
.range("B" & CStr row)).Select
.activecell.formular1c1 = i
row = row + 1

Next
.range("A" & CStr(row)).Select
.selection.entirerow.Insert
.range("A" & CStr(row)).Select
.activecell.formular1c1 = "Total"
.range("B" & CStr(row)).Select
.activecell.formular1c1 = "=SUM(R[-9]B:R[-7]B)"
.range("A1&quot.Select
.Visible = True
End With
Set ExcelApp = Nothing



I am gettin an runtime error and the code stops at the 'sum' line.
Any help is highly appreciated.

 

[RobBroekhuis]

Your SUM formula needs to have "C"s where there are "B"s!
Try also to use the cells property: instead of

.range("A" & CStr(row)).Select
.activecell.formular1c1 = i

,

try

.cells(row,1)=i

Rob

 

[AgentM]

The cells property worked just fine.
Thank you.
Another Q:-

How do you draw a border for a cell thru VB?

Thank you

 

[RobBroekhuis]

Easiest way to find out how to do things in VBA is record a macro using the macro recorder, and look at the code it generates. That way you can make sense of the objects and properties you need.
Good luck
Rob

 

[AgentM]

I tried that, still workin it though.