Yes, that's perfect. Thanks again, Anant.
On Sep 29, 10:01 pm, Anant Basant
<AnantBas...@discussions.microsoft.com> wrote:
> 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
>
> "Smartin" wrote:
> > 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 ------------
Similar Threads
