0

I'm attempting to find a cell containing text. I am able to do so if I perform the search in the same worksheet where the code is defined. But if I perform the search in another worksheet, the search fails.

NOTE: I expect that the issue here is that I don't fully understand how to handle traversing between different worksheets/modules/objects.

My workbook has two worksheets. The first is called 'Alpha': IMG1

The second worksheet is called 'Beta': IMG2

And here is the code which I have defined on the Alpha worksheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Country As String
Dim Element As String
Dim BetaWorksheet As Worksheet
Dim MyRange As Range

    ' A friendly message to let us know that we've entered BeforeDoubleClick
    MsgBox ("You double clicked: " & Selection.Value)

    ' The following code works as expected.
    Country = "Bosnia and Herzegovina"
    MsgBox ("That's nice, but let's find Bosnia and Herzegovina" & ".")
    FindSomething (Country)
    MsgBox ("Notice that " & Country & " is selected in the 'Alpha' worksheet.")

    ' The next lines of code do not work as expected.
    Element = "Californium"
    MsgBox ("Now let's look for the element " & Element & " in the 'Beta' worksheet.")
    MsgBox ("We start by selecting cell A1 in the 'Beta' worksheet.")
    Set BetaWorksheet = ThisWorkbook.Worksheets("Beta")
    BetaWorksheet.Activate
    Set MyRange = BetaWorksheet.Range("A1")
    MyRange.Select
    MsgBox ("Now that we've selected A1, let's find " & Element & ".")
    FindSomething (Element)
End Sub

Private Sub FindSomething(TheThing As String)
Dim FindResult As Range

    MsgBox ("Current worksheet: " & ActiveSheet.Name)
    MsgBox ("Searching for: " & TheThing)

    Set FindResult = Cells.Find(What:=TheThing, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False)

    If (FindResult Is Nothing) Then
        MsgBox ("Found nothing")
    Else
        FindResult.Activate
    End If
End Sub

To use this problem-reproducing example, go to the Alpha workbook and double click a country. This just kicks off my code.

The first part of the example code attempts to find (and succeeds) a hard-coded country (i.e. Bosnia and Herzegovina).

The second part of the example code attempts to find (but fails) a hard-coded element (i.e. Californium). It seems to me that the problem is that the Cells.Find method is still looking in the Alpha worksheet instead of the Beta worksheet.

Any insights would be greatly appreciated.

BTW... My question is similar to the issue discussed in Finding Cell in another sheet. In that case, the code is defined in a standard module. The difference in my case is that my code is defined on a specific worksheet.

braX
  • 11,506
  • 5
  • 20
  • 33
Brian Watrous
  • 1
  • 1
  • 2
    See [how to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/28700020#28700020). It should help you avoid using `Select` and `Activate`, as well as `Cells` references that don't qualify the `Worksheet` they are on - for example, in `Set FindResult = Cells.Find`. – BigBen Nov 21 '18 at 17:56
  • What @BigBen says. Presumably you actually want to do something rather than select the found cell? You could add a sheet parameter to your second sub. – SJR Nov 21 '18 at 18:30
  • 1
    @BigBen Thanks for the pointer to the excellent thread. Switching away from Select/Activate did the trick. – Brian Watrous Nov 21 '18 at 23:40
  • @SJR That (i.e. adding a worksheet parameter) is exactly what I was planning on doing. I left that out of my example to simplify it. But now I've added the worksheet parameter, and it's quite flexible now. :-) – Brian Watrous Nov 21 '18 at 23:42

0 Answers0