G
Guest
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!
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!