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

[Mackeral]

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.

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

 

[dmt32]

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)

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

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

 

[Mackeral]

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.

 

[smozgur]

@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.