sum across cols using for/ next
 

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!!!!


--
Peter

sum across cols using for/ next
 

check VBA help for WorksheetFunction.

worksheetfunction.sum(yourrange)


"Peter" wrote:

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!!!!


--
Peter

sum across cols using for/ next
 

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
--
Peter


"JMB" wrote:

check VBA help for WorksheetFunction.

worksheetfunction.sum(yourrange)


"Peter" wrote:

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!!!!


--
Peter

sum across cols using for/ next
 

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

--

Gary
Excel 2003


"Peter" wrote in message
...
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!!!!


--
Peter

sum across cols using for/ next
 

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



"Peter" wrote:

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
--
Peter


"JMB" wrote:

check VBA help for WorksheetFunction.

worksheetfunction.sum(yourrange)


"Peter" wrote:

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!!!!


--
Peter

sum across cols using for/ next
 

THANKS JMB! WORKED A TREAT.
--
Peter


"JMB" wrote:

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



"Peter" wrote:

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
--
Peter


"JMB" wrote:

check VBA help for WorksheetFunction.

worksheetfunction.sum(yourrange)


"Peter" wrote:

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!!!!


--
Peter

sum across cols using for/ next
 

thankyou gary,
i will give it a try.
regards
--
Peter


"Gary Keramidas" wrote:

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

--

Gary
Excel 2003


"Peter" wrote in message
...
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!!!!


--
Peter