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 ------------ |
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 |