|
Posted to microsoft.public.excel.misc
|
|
|
VB Code Help - locate and sum
Many thanks Bernie!
"Bernie Deitrick" wrote:
Stan,
Either of these will work:
ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
ActiveCell.FormulaR1C1 = "=SUM(R[-" & ActiveCell.Row - 1 & "]C:R[-1]C)"
though I would change
Set rng = Range("A1:IV65400")....
to
Set rng = Range("A:A").....
to limit the find to column A
HTH,
Bernie
MS Excel MVP
"Stan" wrote in message
...
Via VB code, I'm attempting to locate the row that has the word 'Total' in it
(A4)which will always be in coumn A and then move to the same row in colum B
(B4) and sum that data from the row above the active cell to the top of the
column sum(B3:B1).
Column A Column B
Row1 Test1 45
Row2 Test2 51
Row3 Test3 39
Row4 Total
Here is the code I've written although it doesn't work since the word
'Total' will appear in a different row each time. Any suggestions on how to
locate the word 'Total' then move to the immediate right and sum up the data
above it?
Cells(1, 1).Select
Set rng = Range("A1:IV65400").Find(What:="Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Range(rng, rng.End(xlToRight)).Offset(0, 1).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUM(R[-42]C:R[-1]C)"
|