1

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?

Jason Nanay
  • 115
  • 1
  • 12
  • Your 'Function' name is 'MYFIND' but then, in your code you don't use it in any form. Also, very rarely you use a 'MsgBos' inside a 'Function' that returns a value.What exactly are you trying to achieve ? Perhaps you can share a screen-shot of your data, and desired result – Shai Rado Feb 21 '21 at 22:48
  • Hey Shai, I corrected the return variable name (edited the code above). It was a copy paste error. I set MYFIND = 0 which always return 0 but use MsgBox instead for testing purpose. When run the code I'm expecting to see the message "found item [row-num]". – Jason Nanay Feb 21 '21 at 22:57
  • Idea is once I get the code working to find what I need then i would set the value to the return variable which would set the return value in the cell of the excel sheet. I call the user defined function (UDF) from excel using =MYFIND(G2) where "G2" cell consist of the text I search for in the selection. – Jason Nanay Feb 21 '21 at 23:04
  • Just a quick tip: use `Debug.Print` instead of `MsgBox`, saves a click and isn't blocking.It will output to the Immediate window. – Rno Feb 21 '21 at 23:09

2 Answers2

2

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

Shai Rado
  • 33,032
  • 6
  • 29
  • 51
0

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

RetiredGeek
  • 2,980
  • 1
  • 7
  • 21
  • Thanks @RetiredGeek for the suggestion. It is strange that I get value error when use this code. Perhaps because I'm calling it from "Sheet5" ? – Jason Nanay Feb 21 '21 at 23:47
  • Jason, I was calling it from sheet1 so you calling it from sheet5 shouldn't be a problem. As long as Column A values were on sheet4. – RetiredGeek Feb 22 '21 at 01:07