How to skip a step in an Excel Macro if data not found with Cell.Find

The following code snippet is from an Excel macro. It searches a column of data to find a particular string. Then performs a cut and paste of the data into another filed. There are three of these "sections" in my macro. One for "Admin Fee", one for "Distribution of Unassigned" and one for "Locality Transfer".

The issue is that sometimes these strings do not appear in the data. Presently the macro crashes when it doesn't find the string. I would like the macro to simply go to the next step if the string is not found.

I presume an IF - THEN statement is needed but I am not sure of the proper syntax. This is how I envision the error trap:

IF Cell. Find = True THEN
   Next Step ELSE
   GOTO NEXT SECTION

Am I on the right track? Any assistance will be appreciated.

Thanks,
Ray


'Move "Admin Fee" to name field and populate field cell with current tax period
   
    Range("D2").Select
    Cells.Find(What:="Admin Fee", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Cut
    ActiveCell.Offset(0, 2).Select
    ActiveCell.Value = "1"
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    ActiveCell.Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
    End With
ergenbgrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

pls try

    Set oFound = Cells.Find(What:="Admin Fee", After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False)
    If oFound Is Nothing Then 
    ' the code you want to have if you find it
    End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ergenbgrAuthor Commented:
Rgonzo1971,

Thanks you for responding.

This is the code that I entered based upon your suggestion. The Macro stops on the "Set oFound" statement execution. I attached a copy of the error message I received.



Set oFound = Cells.Find(What:="Admin Fee", After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False)
    If oFound Is Nothing Then
   
    ' the code you want to have if you find it
   
    End If
    Selection.Cut
    ActiveCell.Offset(0, 2).Select
    ActiveCell.Value = "1"
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    ActiveCell.Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
    End With
ExcelErrorMessage.jpg
0
Rgonzo1971Commented:
Could you send a dummy file with the code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rory ArchibaldCommented:
BTW it should be:
If Not oFound Is Nothing Then

Open in new window

0
Rgonzo1971Commented:
Tks Rorya
0
ergenbgrAuthor Commented:
Thanks, that certainly makes a lot more sense. I will try that before I upload a sample file.
0
ergenbgrAuthor Commented:
The solution proposed by Rgonzo1971 was almost correct. The syntax should have included "If {Not} oFound Is Nothing Then". I knew that the original didn't sound correct but didn't know what I  needed to change. Thankfully, Rory Archibald caught the error and the solution solved my problem. Thanks to both Experts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.