Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enhance sub to copy cols of variable length into 1 col to snake results into other cols | Excel Programming | |||
2 Cols To 2 Cols VLookup Comparison | Excel Discussion (Misc queries) | |||
Range.Select 1st pass 13 cols, 2nd paqss 25 cols twice as wide in error? | Excel Programming | |||
Totalling x no of cols | Excel Programming | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions |