Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find common text in cell in column in sheet then return total amou | Excel Worksheet Functions | |||
I have a sub total need to find the GST & QST any formula | Excel Worksheet Functions | |||
find text in column h and total the corresponding numbers if colum | Excel Discussion (Misc queries) | |||
function to find which cells add up to a given total? | Excel Worksheet Functions | |||
find parts of a total | Excel Worksheet Functions |