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,

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,

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,

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,

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,

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,

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,