Avoiding Redundancy (.Find Method)

[Smartin]

While somewhat experienced with VBA I am not so experienced working
with Excel objects.

My objective is to loop through multiple worksheets (currently 150+),
find a keyword on each worksheet, and return the value in the cell two
columns to the right. The keyword will exist zero or one times per
sheet.

After studying examples I was able to piece together the following
code, which works. But, I have to think the redundant call to .Find is
unnecessary, as commented in the remark midway.

Any suggestions to improve this? Many thanks.

' ------------ VBA CODE BEGIN ------------
Sub FindInAllSheets()
Const D As String = ";"
Dim sh As Worksheet
Dim r1 As Range

For Each sh In Application.ThisWorkbook.Worksheets
Debug.Print sh.Name & D;
Set r1 = Nothing
sh.Activate
' CAN I AVOID CALLING .Find TWICE HERE?
If Not sh.Cells.Find([parameters]) Is Nothing Then
sh.Cells.Find([parameters]).Activate
Set r1 = Selection
End If
If Not r1 Is Nothing Then
Debug.Print r1.Offset(0, 2).Value
Else
Debug.Print "not found"
End If
Next sh
Worksheets(1).Activate
Set r1 = Nothing
End Sub
' ------------ VBA CODE END ------------

 

[Guest]

Hi Smartin,

Hope the following code is what you want.

Sub FindInAllSheets()
Const D As String = ";"
Dim sh As Worksheet
Dim r1 As Range

For Each sh In Application.ThisWorkbook.Worksheets
Debug.Print sh.Name & D;
Set r1 = Nothing
sh.Activate

Set r1 = sh.Cells.Find([Parameters])

' CAN I AVOID CALLING .Find TWICE HERE?
If Not r1 Is Nothing Then
'value found do something
Debug.Print r1.Offset(0, 2).Value
Else
Debug.Print "not found"
End If
Next sh
Worksheets(1).Activate
Set r1 = Nothing
End Sub

If you need some more tweaking, please write back.

 

[Smartin]

Yes, that's perfect. Thanks again, Anant.

Hi Smartin,

Hope the following code is what you want.

Sub FindInAllSheets()
Const D As String = ";"
Dim sh As Worksheet
Dim r1 As Range

For Each sh In Application.ThisWorkbook.Worksheets
Debug.Print sh.Name & D;
Set r1 = Nothing
sh.Activate

Set r1 = sh.Cells.Find([Parameters])

' CAN I AVOID CALLING .Find TWICE HERE?
If Not r1 Is Nothing Then
'value found do something
Debug.Print r1.Offset(0, 2).Value
Else
Debug.Print "not found"
End If
Next sh
Worksheets(1).Activate
Set r1 = Nothing
End Sub

If you need some more tweaking, please write back.
--
Anant

While somewhat experienced with VBA I am not so experienced working
with Excel objects.

My objective is to loop through multiple worksheets (currently 150+),
find a keyword on each worksheet, and return the value in the cell two
columns to the right. The keyword will exist zero or one times per
sheet.

After studying examples I was able to piece together the following
code, which works. But, I have to think the redundant call to .Find is
unnecessary, as commented in the remark midway.

Any suggestions to improve this? Many thanks.

' ------------ VBA CODE BEGIN ------------
Sub FindInAllSheets()
Const D As String = ";"
Dim sh As Worksheet
Dim r1 As Range

For Each sh In Application.ThisWorkbook.Worksheets
Debug.Print sh.Name & D;
Set r1 = Nothing
sh.Activate
' CAN I AVOID CALLING .Find TWICE HERE?
If Not sh.Cells.Find([parameters]) Is Nothing Then
sh.Cells.Find([parameters]).Activate
Set r1 = Selection
End If
If Not r1 Is Nothing Then
Debug.Print r1.Offset(0, 2).Value
Else
Debug.Print "not found"
End If
Next sh
Worksheets(1).Activate
Set r1 = Nothing
End Sub
' ------------ VBA CODE END ------------