Finding text in one worksheet from code on another sheet
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':
The second worksheet is called 'Beta':
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.
excel vba excel-vba worksheet
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
add a comment |
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':
The second worksheet is called 'Beta':
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.
excel vba excel-vba worksheet
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
2
See how to avoid using Select in Excel VBA. It should help you avoid usingSelectandActivate, as well asCellsreferences that don't qualify theWorksheetthey are on - for example, inSet 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
add a comment |
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':
The second worksheet is called 'Beta':
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.
excel vba excel-vba worksheet
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
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':
The second worksheet is called 'Beta':
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.
excel vba excel-vba worksheet
excel vba excel-vba worksheet
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
edited Nov 22 '18 at 7:55
JohnyL
3,6881924
3,6881924
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
asked Nov 21 '18 at 17:52
Brian WatrousBrian Watrous
11
11
2
See how to avoid using Select in Excel VBA. It should help you avoid usingSelectandActivate, as well asCellsreferences that don't qualify theWorksheetthey are on - for example, inSet 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
add a comment |
2
See how to avoid using Select in Excel VBA. It should help you avoid usingSelectandActivate, as well asCellsreferences that don't qualify theWorksheetthey are on - for example, inSet 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
2
2
See how to avoid using Select in Excel VBA. 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
See how to avoid using Select in Excel VBA. 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
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
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
@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
@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
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417935%2ffinding-text-in-one-worksheet-from-code-on-another-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417935%2ffinding-text-in-one-worksheet-from-code-on-another-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
See how to avoid using Select in Excel VBA. It should help you avoid using
SelectandActivate, as well asCellsreferences that don't qualify theWorksheetthey are on - for example, inSet 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