Find value in cell, if not there then do something

[AJTatsuo]

Hi.

I’ve got a piece of code that hunts for a name based on a name in one data set and will transpose the information related to that name to a collection database.

This is fed by a weekly report; the feed is manually ran by an admin.

Staffing frequently changes so I am trying to identify when the name doesn’t exist in my central database and prompt VBA to create a template for the new person before adding the data.

I have accomplished the majority of this but the issue I’m coming up against is; when an error throws because nothing is found I need to capture this and do one thing in response or if the error isn’t thrown move on.

Complication is I am in a called subroutine from a subroutine in the middle of a loop.

I can’t find a way to convert the error to a true or false.

The GoTo is clunky, only let’s you skip a portion of the code rather than if this else type response; plus every forum I have read warns me off the GoTo spaghetti nonsense.

Have I hit an impossible wall with this.

I am currently using:

Range(“A:AG”).Find(What:=EMDName).Address.Select

to hunt; the variable “EMDName”has been declared and populated before this sub starts.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Dim Fnd As Range
Set Fnd = Range("A:AG").Find(EMDName)
If Not Fnd Is Nothing Then
   MsgBox "Not found"
   Exit Sub
End If