[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1245
  • Last Modified:

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
0
ergenbgr
Asked:
ergenbgr
  • 3
  • 3
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now