Solved

Find Function Excel 2007

Posted on 2014-10-15
4
152 Views
Last Modified: 2014-10-24
How can I construct a VBA statement depending on the results of the Find statement below?

Cells.Find(What:="Canceled", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
           :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
           False, SearchFormat:=False).Activate

If the above Find statement does not find what I am looking for and the error message "Microsoft Office Excel cannot find the data you're looking for" displays, I want the search to terminate and go onto a named macro.  If it does find the value then, I want it to continue to another named macro.

Bill J.
0
Comment
Question by:cowboywm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Expert Comment

by:slubek
ID: 40382782
Try to get an output from Cells.Find method:
On Error Resume Next
  Set rFound = Cells.Find (...)
On Error GoTo 0
  If rFound Is Nothing Then (...)

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40382929
Try to avoid using error trapping functionally. It can get very confusing if you actually get an unexpected error.
    Dim rngFound As Range
    Set rngFound = ActiveSheet.Cells.Find(What:="Canceled", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                   :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                   False, SearchFormat:=False)
    If rngFound Is Nothing Then
        Call NotFoundMacro
    Else
        Call FoundMacro(rngFound)
    End If

Open in new window

0
 

Accepted Solution

by:
cowboywm earned 0 total points
ID: 40385683
This statement worked a couple of times but continued to loop each time I ran it.  It cannot loop back to the first instance of "Canceled".  I may have made a typo when trying to update the code but my eyes can't find one.  The macro must stop at that last instance so I can execute a sort procedure.

   Dim rngFound As Range
    Set rngFound = ActiveSheet.Cells.Find(What:="Canceled", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                   :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                   False, SearchFormat:=False)
    If rngFound Is Nothing Then
        Call NotFoundMacro
    Else
        Call FoundMacro(rngFound)
    End If

The starting macro is called "Prepare_For_Sort" and must be run on the Attendance sheet.

The purpose of this macro is to prepare the sheet for sorting since sorting cannot happen on merged cells.  I need to unmerge all instances of them no matter how many there may be  (currently there are only 2).  The sort needs to start at row 2 and continue to the last row.  As teammates come and go, the number of rows will change.

I appreciate the help and a second pair of eyes.  See attached Work Plan Master.xlsm file.
Work-Plan-Master.xlsm
0
 

Author Closing Comment

by:cowboywm
ID: 40401566
I decided it was much easier to color the cells black in an unmerged state to allow for easier sorting later on.  Thank you for the input.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question