asked on

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Ergenbright

ASKER

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

Rgonzo1971

Could you send a dummy file with the code?

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

Tks Rorya

Ray Ergenbright

ASKER

Thanks, that certainly makes a lot more sense. I will try that before I upload a sample file.

Ray Ergenbright

ASKER

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.