Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing with a "Find"
When a value is not found, the macro errors and stops. How can I trap the
error before it is reported and act on the error with out stopping the macro? Columns("F:F").Select Selection.Find(What:="347", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False).Activate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing with a "Find"
You tell it to activate, but if it hasn't found anything, what is is
supposed to activate. Remove the activate, remove the problem. Dim rng as Range set rng = Columns("F:F").Find(What:="347", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False) if not rng is nothing then rng.activate Else msgbox "Not found" End if -- Regards, Tom Ogilvy PGibson wrote in message ... When a value is not found, the macro errors and stops. How can I trap the error before it is reported and act on the error with out stopping the macro? Columns("F:F").Select Selection.Find(What:="347", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False).Activate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing with a "Find"
for some reason I need to select the range before using the set rng. this
works with the range select the other errors with unable to set column property ?? Can you explain? Sub AddToTotals(FindText As String) Range("f:f").Select Set RNG = Columns("F:F").Find(What:=FindText, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False) If Not RNG Is Nothing Then RNG.Activate Call AddUp Else End If End Sub "Tom Ogilvy" wrote in message ... You tell it to activate, but if it hasn't found anything, what is is supposed to activate. Remove the activate, remove the problem. Dim rng as Range set rng = Columns("F:F").Find(What:="347", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False) if not rng is nothing then rng.activate Else msgbox "Not found" End if -- Regards, Tom Ogilvy PGibson wrote in message ... When a value is not found, the macro errors and stops. How can I trap the error before it is reported and act on the error with out stopping the macro? Columns("F:F").Select Selection.Find(What:="347", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False).Activate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing with a "Find"
When you selected the column, the active cell was in that column.
I like this to find the first occurance in a range: Option Explicit Sub AddToTotals(FindText As String) 'Range("f:f").Select Dim RNG As Range With ActiveSheet.Columns("F:F") Set RNG = .Cells.Find(What:=FindText, After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False) End With If Not RNG Is Nothing Then RNG.Activate Call AddUp Else End If End Sub PGibson wrote: for some reason I need to select the range before using the set rng. this works with the range select the other errors with unable to set column property ?? Can you explain? Sub AddToTotals(FindText As String) Range("f:f").Select Set RNG = Columns("F:F").Find(What:=FindText, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False) If Not RNG Is Nothing Then RNG.Activate Call AddUp Else End If End Sub "Tom Ogilvy" wrote in message ... You tell it to activate, but if it hasn't found anything, what is is supposed to activate. Remove the activate, remove the problem. Dim rng as Range set rng = Columns("F:F").Find(What:="347", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False) if not rng is nothing then rng.activate Else msgbox "Not found" End if -- Regards, Tom Ogilvy PGibson wrote in message ... When a value is not found, the macro errors and stops. How can I trap the error before it is reported and act on the error with out stopping the macro? Columns("F:F").Select Selection.Find(What:="347", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False).Activate -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How to change the default in Excel from "find next" to "find all" | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) |