+ Reply to Thread
Results 1 to 4 of 4

find and replace crashes when value is not present.

Hybrid View

  1. #1
    Registered User
    Join Date
    10-23-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    11

    find and replace crashes when value is not present.

    hello,

    I created a macro that I want to use across all my workbooks, it finds abbreviation and replaces it with the full name. Unfortunately it crashes when the abbreviation I am looking for is not there. If anyone can help me I appreciate it. my code and the file is attached.

    
    Sub rename()
    '
    ' rename Macro
    '
    
    '
        Selection.Replace What:="bc", Replacement:="back curb", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="ea", Replacement:="edge asphalt", LookAt:= _
            xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="gr", Replacement:="guard rail", LookAt:=xlWhole _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="mh", Replacement:="manhole", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="mh", Replacement:="manhole", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="mhsa", Replacement:="manhole sanitary", LookAt:= _
            xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="sp", Replacement:="sign", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="cull", Replacement:="culvert", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="culs", Replacement:="culvert", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="pp", Replacement:="power pole", LookAt:=xlWhole _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("F16").Select
        ActiveWindow.SmallScroll Down:=21
        Cells.Replace What:="ec", Replacement:="edge of concrete", LookAt:= _
            xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("F42").Select
        Cells.Replace What:="ec1", Replacement:="edge of concrete1", LookAt:= _
            xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("G42").Select
        ActiveWindow.SmallScroll Down:=18
        Cells.Replace What:="brg", Replacement:="bridge", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("F57").Select
        ActiveWindow.SmallScroll Down:=12
        Cells.Replace What:="vw", Replacement:="water valve", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("F73").Select
        ActiveWindow.SmallScroll Down:=12
        Cells.Replace What:="fhy", Replacement:="fire hydrant", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Cells.Find(What:="usa", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        Cells.Replace What:="usa", Replacement:="undergrond sanitary sewer", _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
            :=False, ReplaceFormat:=False
        Range("F86").Select
        ActiveWindow.SmallScroll Down:=21
        ActiveWindow.WindowState = xlNormal
        ActiveWindow.WindowState = xlNormal
        Cells.Replace What:="eusd", Replacement:="edge of unsurfaced driveway", _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
            :=False, ReplaceFormat:=False
        Range("F106").Select
        ActiveWindow.SmallScroll Down:=18
        Cells.Replace What:="upw", Replacement:="underground power", LookAt:= _
            xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: find and replace crashes when value is not present.

    Crashes on what line with what error?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-23-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: find and replace crashes when value is not present.

    Cells.Find(What:="usa", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate"
    crashes at that line because "usa" is not present on the current workbook.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: find and replace crashes when value is not present.

    Set an object variable and test it:

    dim rFind as range
    
    set rFind = Cells.Find(all that stuff)
    if rFind is nothing then
        msgbox "not found"
    else
        rFind.Activate
    endif

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1