code to find stuff in each sheet?
 

I want to write some code to loop through all the w/sheets in the
current open workbook and inspect various columns within each sheet.

I understand how to define and loop through the worksheet collection, I
just don't know how to code the 'inspection' part ... so I've got the
outline as follows:

Private Sub Worksheet_Activate()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
... my code to inspect, for example, column P within the wsheet
defined by sh and return a value based on various criteria

Next

but not the detail.

Anyone help....

code to find stuff in each sheet?
 

Hi,
Simple example of using FIND on column P. If you have multiple
criteria then you will probably use IF ..THEN .. ELSE constructs rather than
(or in conjunction with ) FIND.

Without knowing your criteria it is difficult to more specific.

HTH

Private Sub Worksheet_Activate()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
myValue = "ABC"
With sh.Columns("P:P")
' example of FIND ......look for "ABC" and replace by "XYZ"
Set c = .Find(myValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
' your code here
c.Value = "XYZ"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next

End Sub

" wrote:

I want to write some code to loop through all the w/sheets in the
current open workbook and inspect various columns within each sheet.

I understand how to define and loop through the worksheet collection, I
just don't know how to code the 'inspection' part ... so I've got the
outline as follows:

Private Sub Worksheet_Activate()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
... my code to inspect, for example, column P within the wsheet
defined by sh and return a value based on various criteria

Next

but not the detail.

Anyone help....

code to find stuff in each sheet?
 

Hi,

That does start to help me ...

But, instead of the find, how would I loop through each of the cells
in, for example, column P and inspect them one at a time?

I', not looking for a specific value particularly, I'm trying to find a
cell which satifies a certain criteria e.g. it must come before the
next zero value ...

TIA

code to find stuff in each sheet?
 

Dim rng as Range, cell as Range
set rng = Range(cells(1,"P"),Cells(rows.count,"P").End(xlup) )
for each cell in rng
if cell.Offset(1,0).Value = 0 then
msgbox cell.Address
exit for
end if
Next

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Hi,

That does start to help me ...

But, instead of the find, how would I loop through each of the cells
in, for example, column P and inspect them one at a time?

I', not looking for a specific value particularly, I'm trying to find a
cell which satifies a certain criteria e.g. it must come before the
next zero value ...

TIA

code to find stuff in each sheet?
 

thanks Tom.

could I put the above code insdie the loop that 'reads' thru all the
worksheets?

or do I have add some reference to the worksheet?

code to find stuff in each sheet?
 

Sub InspectSheets
Dim rng as Range, cell as Range
Dim sh as Worksheet

for each sh in Worksheets
set rng = sh.Range(sh.cells(1,"P"),sh.Cells(rows.count,"P"). End(xlup))
for each cell in rng
if cell.Offset(1,0).Value = 0 then
msgbox cell.Address(0,0,xlA1,True)
exit for
end if
Next cell
Next sh
End Sub

Obviously tailor the code to fit the condition you are looking for.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
thanks Tom.

could I put the above code insdie the loop that 'reads' thru all the
worksheets?

or do I have add some reference to the worksheet?

code to find stuff in each sheet?
 

Tom,

I managed to tailor the code to get it to do exactly what I wanted.

thanks a lot for your help!