VB Code Help - locate and sum
 

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

VB Code Help - locate and sum
 

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

VB Code Help - locate and sum
 

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


In article ,
Stan wrote:

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

VB Code Help - locate and sum
 

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

--
Don Guillett
SalesAid Software

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

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