If Worksheet is not shown at Macro Start, .find() is nothing-CodePudding

My Problem is for me very weird. I was searching and reading other VBA problems, which look like to me to my problem more equal. I don't know.

use case: The macro should activate/select a sheet in a active workbook. Then find in that active worksheet a date/string. If its found it will save the range to an variable. In case the variable is nothing, then the macro should stop.

problem: If the sheet was not selected/activated by start of the macro, then the .find() is not working. Every Time I got a "nothing" back. If the worksheet was selected/activated by macro start (show on my monitor), then it works.

I checked the problem on a other PC as well. But it happens there as well...

    Dim rngFirst As Range
    Dim rngNext As Range
    Dim rngArea As Range
    Set rngArea = Range("A:Z")
    Dim defaultValue As Date, insertedDate As Date
    defaultValue = Format(Date, "dd.mm.yyyy")
    insertedDate = Application.InputBox(message, title, Format(defaultValue, "dd.mm.yyyy"), Type:=1)

    ActiveWorkbook.Sheets("mySheet").Activate
    Do
      If rngFirst Is Nothing Then
         Set rngFirst = rngArea.Find(What:=insertedDate, After:=rngArea(1))     'the problem start with this code line. What I got back is "Nothing"
         Set rngNext = rngFirst
         If rngNext Is Nothing Then
            MsgBox "There was a problem with finding the meeting date. Please check!"   Chr(10)   "The Macro will end here."
            Exit Sub
         End If
      Else
         'Do something with what was found
         'find the next entry in the sheet
         Set rngNext = rngArea.Find(What:=insertedDate, After:=rngNext)
         If rngNext.Address = rngFirst.Address Then Exit Do
      End If
    Loop

Note: Unfortunatly I dont have Administrator on my working PC... But I think that don't make a diffrence.

CodePudding user response：

It is solved. It was a wrong thinking way... The comment under my question explains the solution.

Here is the working code:

    Dim rngFirst As Range
    Dim rngNext As Range
    Dim rngArea As Range
    Set rngArea = Sheets("mySheet").Range("A:Z")       'added solution here
    Dim defaultValue As Date, insertedDate As Date
    defaultValue = Format(Date, "dd.mm.yyyy")
    insertedDate = Application.InputBox(message, title, Format(defaultValue, "dd.mm.yyyy"), Type:=1)

    Do
      If rngFirst Is Nothing Then
         Set rngFirst = rngArea.Find(What:=insertedDate, After:=rngArea(1))     'the problem start with this code line. What I got back is "Nothing"
         Set rngNext = rngFirst
         If rngNext Is Nothing Then
            MsgBox "There was a problem with finding the meeting date. Please check!"   Chr(10)   "The Macro will end here."
            Exit Sub
         End If
      Else
         'Do something with what was found
         'find the next entry in the sheet
         Set rngNext = rngArea.Find(What:=insertedDate, After:=rngNext)
         If rngNext.Address = rngFirst.Address Then Exit Do
      End If
    Loop