VB Code Help - locate and sum

[Guest]

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)"

 

[Bernie Deitrick]

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

 

[JE McGimpsey]

One way:

Dim rFound As Range
With ActiveSheet.Columns(1).Cells
Set rFound = .Find( _
After:=.Cells(.Count), _
What:="Total", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
End With
If Not rFound Is Nothing Then _
rFound.Offset(0, 1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"

 

[Don Guillett]

Sub findtotal()
x = Columns(1).Find("total").Row
Cells(x, "b") = Application.Sum(Range(Cells(2, "b"), Cells(x - 1, "b")))
End Sub

 

[Guest]

Many thanks Bernie!

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

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)"