Solved

Find Function Excel 2007

Posted on 2014-10-15
4
131 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
This article will show you how to use shortcut menus in the Access run-time environment.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now