Question: Looking for some expert advise on Excel VBA. I have written a small piece of code that selects a column in a specific sheet and perform a find/search on the selection. When I call the function from Excel it doesn't find anything as the Range value returned by find is empty. However when I run it directly within developer window it works fine.

Please note I'm aware same could be done using existing Excel macros. But I don't want to use them for a reason. Hope you understand.

Here is the code;

```
Function MYFIND(x As Range) As Integer
Dim Cell As Range
Worksheets("Sheet4").Select
Columns("A:A").Select
Set Cell = Selection.Find(What:=x.Value, searchDirection:=xlNext, MatchCase:=False)

If Cell Is Nothing Then
    MsgBox "search item " & x.Value & " not found in " & ActiveSheet.Name
Else
    MsgBox "Found item " & Cell.Row
End If
MYFIND = 0 
End Function

```

What could be the problem?

Answer: 
Try using the UDF below, try as much as you can to avoid using `Select` and `Selection.

***Modified UDF***

```
Function MYFIND(x As Range) As Long

Dim Rng As Range
Dim Cell As Range

' set the range, never use select or selection
Set Rng = Worksheets("Sheet4").Columns("A:A")
Set Cell = Rng.Find(What:=x.Value, LookIn:=xlValues, lookAt:=xlWhole, searchDirection:=xlNext)

If Cell Is Nothing Then
    MYFIND = 0 ' returns 0 if not found
Else
    MYFIND = Cell.Row
End If

End Function

```

Then, call your UDF from your worksheet:

[![enter image description here](https://i.stack.imgur.com/7IxQT.png)](https://i.stack.imgur.com/7IxQT.png)
Jason,

How about this?

```
Option Explicit

Function MYFIND(y As String) As Integer

Dim Cell      As Range
Dim GLAccount As Long
Dim x         As Range

Set x = Range(y)

Set Cell = Sheet4.Columns("A:A").Find(What:=x.Value, searchDirection:=xlNext, MatchCase:=False)

If Cell Is Nothing Then
    MsgBox "search item " & x.Value & " not found in " & ActiveSheet.Name
Else
    MsgBox "Found item " & Cell.Row
End If

  MYFIND = Cell.Row()

End Function

```

Call as:

```
=MyFind("E1")

```

Where E1 contains the value you're looking for.
I set up letters a-f in col A of Sheet4 (used the code name in the code) and it returned 3 as c is in row 3.

HTH