ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sum (https://www.excelbanter.com/excel-programming/342412-sum.html)

tjh

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,

Bob Phillips[_6_]

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,




tjh

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,





tjh

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,







Bob Phillips[_6_]

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,









tjh

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,










Bob Phillips[_6_]

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