The "Find" operator in VBA suddently doens't work.

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
225
Office Version
  1. 365
Platform
  1. Windows
You can see the comments I've made to make this routine work now. It worked in it's original form 2 or 3 days ago.

The issue is that it use to (and should basically) work on a non-Active sheet.
To make it work with now, I had to change the code to make the sheet with the data to be searched Active.
VBA Code:
Function Find_String(Sheet_Spec, _
                  Rng, Look_For, _
                  Optional Answer_Row, Optional Answer_Col, _
                  Optional LookAt As Look_Ats = xlWhole, _
                  Optional Case_Arg As Boolean = False) As Boolean
 
    ' Lookup a String in a Sheet and return its address in ROW and COL.
    ' Note: The Sheet must be in the currently opened Workbook.
    ' Note: LookAt can have values "xlPart" or "xlWhole".
    ' 12/22/10 Replaced Find_Sub. WML
    ' 12/22/10 Replaced xlLookAts Enum. WML
    ' 7/24/15 Made all args after Look_For optional. WML
    ' 3/28/18 Added call to "Make_Range_Special".  WML
    ' 9/4/19 Changed "Case_Arg" option to False. WML
    ' 1/15/20 Added check to see if finding the same row, in which case it's an error. WML
    '  5/12/20 Changed code because of Microsoft change. WML
    
    Dim SHEET As Worksheet
    
    Prog = "Find_String"
    
    Curr_Sheet = ActiveSheet.Name   '  <-- Really?
    Call Sheet_Arg(Sheet_Spec, SHEET, Sheet_Name)
    SHEET.Activate                  '  < And this too?
    
    With SHEET.Range(Rng)
                        
        Set HOLD = Selection.Find(What:=Look_For, _
                        After:=ActiveCell, _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
    End With
            
    If HOLD Is Nothing Then
        Find_String = False
    Else
        ' Extra test to be sure
        Answer_Row = HOLD.Row
        Answer_Col = HOLD.Column
        Find_String = ( Look_For = Cells(Answer_Row, Answer_Col))
    End If
    
    Sheets(Curr_Sheet).Activate               '  <-- And this too.
    
End Function ' Find_String
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi,

Suggest passing the search range to your function as this will also pass the parent of the range (worksheet) which should resolve the sheet not being active issue.

I would also suggest using the Enumerated constants available to the Range.Find Method in your function as these will produce the familiar IntelliSense for each of the parameters.


Example (untested)

VBA Code:
Function FindString(ByVal Target As Range, ByVal Look_For As Variant, _
                    Optional After As Range, Optional LookIn As XlFindLookIn, _
                    Optional LookAt As XlLookAt, Optional SearchOrder As XlSearchOrder, _
                    Optional SearchDirection As XlSearchDirection) As Variant
    Dim HOLD As Range
    If After Is Nothing Then Set After = Target.Cells(1, 1)
    
    Set HOLD = Target.Find(what:=Look_For, After:=After, _
                            LookIn:=LookIn, LookAt:=LookAt, _
                            SearchOrder:=SearchOrder, SearchDirection:=SearchDirection, _
                            MatchCase:=False, SearchFormat:=False)
    If Not HOLD Is Nothing Then
    
        FindString = HOLD.Address
    Else
        FindString = False
    End If
End Function

to call

VBA Code:
Sub TestFindString()
    Dim Search As Variant
   Search = FindString(Sheets("Sheet10").Range("A10:A100"), "", Range("A30"), xlValues, xlWhole, xlByRows, xlNext)
   
   MsgBox Search
End Sub

Sugestion is just an idea which you will need to adjust to your specific project need

Hope Helpful

Dave
 
Upvote 0
Solution
Thanks for your reply, Dave.

The darn thing started to work the way I expected it to. Don't now if i had some cruddy code in it, or every once and a while I find VBA itself gives a strange response for a while and then gets corrected.
 
Upvote 0
@Mackeral - Glad to hear that your code started to work somehow. However, the last post of yours that you marked as the solution doesn't contain a solution. In fact, the sample code in the original post is not complete either (missing referenced procedure, unassigned variable, etc.). So, there might be actually many reasons why your code fails that helpers can't find out by looking at the code snippet you posted.

On the other side, the sample code that @dmt32 posted can be used as a generic implementation of a search module. Therefore, I switched the marked solution post as it will help future readers.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top