Find Text and put total across from it
 

I have the following code:

Sub UpdateTotal()
'
' UpdateTotal Macro
' Macro recorded 3/23/2005 by Stacey Macumber
'
' Keyboard Shortcut: Ctrl+u
'
Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet
For Each cell In .Range("e1:e5000")
If cell.Interior.ColorIndex = 16 Then
dblSum = dblSum + cell.Offset(0, 0).Value
End If
Next
Application.EnableEvents = False

.Range("A2917").Value = dblSum
.Range("A2917").Activate

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Where the line .Range("A2917").Value = dblSum instead I want the macro to
look for the word TOTAL and then place the .Value on the same row but 5
columns over, the reason for this is TOTAL can be on a different row each
time the report is ran so I can't do the set thing "A2917" like I have been
(by just changing the cell as needed).
I haven't been very success with trying other code.

Any help would be great!

Find Text and put total across from it
 

smac,
replace both .value = and the .activate code with this code


dim totalr as range
set totalr = cells.find _(what:="TOTAL", _
lookin:=xlvalues, lookat:=xlwhole,matchcase:=true)
if totalr is nothing then
msgbox "Could not find TOTAL"
exit sub
end if
totalr.activate
activecell.offset(0,5).value = dblsum


ben


"SMac" wrote:

I have the following code:

Sub UpdateTotal()
'
' UpdateTotal Macro
' Macro recorded 3/23/2005 by Stacey Macumber
'
' Keyboard Shortcut: Ctrl+u
'
Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet
For Each cell In .Range("e1:e5000")
If cell.Interior.ColorIndex = 16 Then
dblSum = dblSum + cell.Offset(0, 0).Value
End If
Next
Application.EnableEvents = False

.Range("A2917").Value = dblSum
.Range("A2917").Activate

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Where the line .Range("A2917").Value = dblSum instead I want the macro to
look for the word TOTAL and then place the .Value on the same row but 5
columns over, the reason for this is TOTAL can be on a different row each
time the report is ran so I can't do the set thing "A2917" like I have been
(by just changing the cell as needed).
I haven't been very success with trying other code.

Any help would be great!

Find Text and put total across from it
 

Works like a charm!

"ben" wrote:

smac,
replace both .value = and the .activate code with this code


dim totalr as range
set totalr = cells.find _(what:="TOTAL", _
lookin:=xlvalues, lookat:=xlwhole,matchcase:=true)
if totalr is nothing then
msgbox "Could not find TOTAL"
exit sub
end if
totalr.activate
activecell.offset(0,5).value = dblsum


ben


"SMac" wrote:

I have the following code:

Sub UpdateTotal()
'
' UpdateTotal Macro
' Macro recorded 3/23/2005 by Stacey Macumber
'
' Keyboard Shortcut: Ctrl+u
'
Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet
For Each cell In .Range("e1:e5000")
If cell.Interior.ColorIndex = 16 Then
dblSum = dblSum + cell.Offset(0, 0).Value
End If
Next
Application.EnableEvents = False

.Range("A2917").Value = dblSum
.Range("A2917").Activate

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Where the line .Range("A2917").Value = dblSum instead I want the macro to
look for the word TOTAL and then place the .Value on the same row but 5
columns over, the reason for this is TOTAL can be on a different row each
time the report is ran so I can't do the set thing "A2917" like I have been
(by just changing the cell as needed).
I haven't been very success with trying other code.

Any help would be great!