![]() |
Sum
Hello,
Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
Sum
Try this code
Sub Test() Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMIF(R1C12:R" & iLastRow & "C12,""RS"",R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i End Sub -- HTH Bob Phillips "tjh" wrote in message ... Hello, Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
Sum
Thats great thanks,
One more question -- How about if the column L cell does not contain "RS" and is not blank "Bob Phillips" wrote: Try this code Sub Test() Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMIF(R1C12:R" & iLastRow & "C12,""RS"",R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i End Sub -- HTH Bob Phillips "tjh" wrote in message ... Hello, Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
Sum
Sorry, I meant, How can I sum the column if the value in column L does not
equal "RS" and is not blank? Preferably placing a formula in the bottom cell adding up everything that does not have the value "RS" in column L and is not blank in column L. "Bob Phillips" wrote: What do you mean? If it does not contain RS then the SUMIF will ignore it. -- HTH Bob Phillips "tjh" wrote in message ... Thats great thanks, One more question -- How about if the column L cell does not contain "RS" and is not blank "Bob Phillips" wrote: Try this code Sub Test() Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMIF(R1C12:R" & iLastRow & "C12,""RS"",R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i End Sub -- HTH Bob Phillips "tjh" wrote in message ... Hello, Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
Sum
Ok I see. A bit trickier
Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMPRODUCT(--(R1C12:R" & iLastRow & "C12<""RS"")," & _ "--(R1C12:R" & iLastRow & "C12<""""),R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i -- HTH Bob Phillips "tjh" wrote in message ... Sorry, I meant, How can I sum the column if the value in column L does not equal "RS" and is not blank? Preferably placing a formula in the bottom cell adding up everything that does not have the value "RS" in column L and is not blank in column L. "Bob Phillips" wrote: What do you mean? If it does not contain RS then the SUMIF will ignore it. -- HTH Bob Phillips "tjh" wrote in message ... Thats great thanks, One more question -- How about if the column L cell does not contain "RS" and is not blank "Bob Phillips" wrote: Try this code Sub Test() Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMIF(R1C12:R" & iLastRow & "C12,""RS"",R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i End Sub -- HTH Bob Phillips "tjh" wrote in message ... Hello, Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
Sum
Thank You. These are perfect!!!
I was able to follow most of the code, but what does the "--" symbol do to the formula. I cannot understand why this makes it work, but without it the formula does not? Is this because it is a not equals to? "Bob Phillips" wrote: Ok I see. A bit trickier Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMPRODUCT(--(R1C12:R" & iLastRow & "C12<""RS"")," & _ "--(R1C12:R" & iLastRow & "C12<""""),R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i -- HTH Bob Phillips "tjh" wrote in message ... Sorry, I meant, How can I sum the column if the value in column L does not equal "RS" and is not blank? Preferably placing a formula in the bottom cell adding up everything that does not have the value "RS" in column L and is not blank in column L. "Bob Phillips" wrote: What do you mean? If it does not contain RS then the SUMIF will ignore it. -- HTH Bob Phillips "tjh" wrote in message ... Thats great thanks, One more question -- How about if the column L cell does not contain "RS" and is not blank "Bob Phillips" wrote: Try this code Sub Test() Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMIF(R1C12:R" & iLastRow & "C12,""RS"",R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i End Sub -- HTH Bob Phillips "tjh" wrote in message ... Hello, Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
Sum
Not it is not 'not equal to', but coercing to a numeric value. Take a look
at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for more info. -- HTH Bob Phillips "tjh" wrote in message ... Thank You. These are perfect!!! I was able to follow most of the code, but what does the "--" symbol do to the formula. I cannot understand why this makes it work, but without it the formula does not? Is this because it is a not equals to? "Bob Phillips" wrote: Ok I see. A bit trickier Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMPRODUCT(--(R1C12:R" & iLastRow & "C12<""RS"")," & _ "--(R1C12:R" & iLastRow & "C12<""""),R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i -- HTH Bob Phillips "tjh" wrote in message ... Sorry, I meant, How can I sum the column if the value in column L does not equal "RS" and is not blank? Preferably placing a formula in the bottom cell adding up everything that does not have the value "RS" in column L and is not blank in column L. "Bob Phillips" wrote: What do you mean? If it does not contain RS then the SUMIF will ignore it. -- HTH Bob Phillips "tjh" wrote in message ... Thats great thanks, One more question -- How about if the column L cell does not contain "RS" and is not blank "Bob Phillips" wrote: Try this code Sub Test() Dim iLastRow As Long Dim i As Long For i = 16 To 27 iLastRow = Cells(Rows.Count, i).End(xlUp).Row If iLastRow = 1 And Cells(1, i).Value = "" Then Else Cells(iLastRow + 1, i).FormulaR1C1 = _ "=SUMIF(R1C12:R" & iLastRow & "C12,""RS"",R1C" & _ i & ":R" & iLastRow & "C" & i & ")" End If Next i End Sub -- HTH Bob Phillips "tjh" wrote in message ... Hello, Is there and easy way to total values using vba. I have a spreadsheet with a few thousand rows and I would like VBA to go through the worksheet, and for anything that has RS in column L -- I want it to take the value in columns P thru AA and sum them at the bottom and also include the formula to the cell reference. A different total for each column. Thanks, |
All times are GMT +1. The time now is 10:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com