sum across cols using for/ next

[Peter]

I am trying to sum across a known number of cols in a worksheet and I want
to use :

1 the for/ next style of programming

2 the cells property of the range object



For example, this sub multiplies all cells in a range by 100, using the
above two criteria for the construction of the code.





Sub percnt()

'

'

''>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'' 1 CONVERT ratios to per cent by multiplying by 100

''>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'

finalentry = Cells(65536, 1).End(xlUp).Row



For i = 1 To finalentry



If i > 2 Then



For j = 2 To 15



If Cells(2, j).Value = "PC" Then



Cells(i, 16).Formula = Cells(i, j) * 100 ‘$$$$$$$$$$$$$

Cells(i, 16).NumberFormat = "#,##0.0"

End If



Next j



End If



Next i





End Sub

''>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



The line marked with ‘$$$$$$$$$ is the line I want you to focus on pls.



I want to sum across the cols 2 to 15 and write the outcome in col 16.



So I use the excel sum function of the form sum(element1, element 2) right?



Like this:

Cells(i, 16).Formula = Sum(Cells(i, j))



But this wont work. I have tried looking up the help thingy but I cannot
for the life of me find a reference to help me out.



I am so frustrated with this and I have tried. And tried. and its holding me
up.



Pls help!!!!

 

[JMB]

check VBA help for WorksheetFunction.

worksheetfunction.sum(yourrange)

 

[Peter]

hi jmb,

thanks for responding. i did wot you said.
but to no avail. could you pls write our the the correct syntax i need for
the referenced statement (just one line?) and post it here pls.
many thanks

 

[Gary Keramidas]

don't know exactly what you're trying to do or how your data is structured,
but give this a try

Sub percnt()

''>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'' 1 CONVERT ratios to per cent by multiplying by 100

''>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Dim finalentry As Long
Dim i As Long, j As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
finalentry = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To finalentry
For j = 2 To 15
With ws
If .Cells(2, j).Value = "PC" Then
.Cells(i, 16).Formula = "=sum(" & .Cells(i, _
1).Address & ":" & .Cells(i, 15).Address &
_
") * 100"
.Cells(i, 16).NumberFormat = "#,##0.0"
End If
End With
Next
Next i

End Sub

 

[JMB]

worksheetfunction.sum(range(cells(i,1), cells(i,15)))

 

[Peter]

THANKS JMB! WORKED A TREAT.
--
Peter

worksheetfunction.sum(range(cells(i,1), cells(i,15)))

 

[Peter]

thankyou gary,
i will give it a try.
regards