Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding Redundancy (.Find Method)
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 ------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding Redundancy (.Find Method)
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 ------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding Redundancy (.Find Method)
Yes, that's perfect. Thanks again, Anant.
On Sep 29, 10:01 pm, Anant Basant 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 ------------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xls vba find method to find row that contains the current date | Excel Programming | |||
Using variables to make a date and using find method to find that. | Excel Programming | |||
How do I correct a cyclic redundancy check error | Excel Discussion (Misc queries) | |||
assignment redundancy check | Excel Discussion (Misc queries) | |||
cyclic redundancy check | Excel Programming |