Searching in specific sheets problem

[Pawel]

Hi guys,
I have this macro, and I can't make it perform search only in specific sheets ("aaa","bbb","ccc" in this case).\
current code returns MsgBox "No purple field found" even if I put it in one of those sheets on purpose.
I would also like my macro to select and show first found cell ( even if currently other sheet is open).
Please help.


Dim cell As Range
Dim SearchRange As Range
Dim c As Range
Dim shtfound As Boolean
sthfound = False
On Error Resume Next
Set SearchRange = ThisWorkbook.Worksheets(Array("bbb", "aaa", "ccc")).UsedRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not SearchRange Is Nothing Then
With Application.FindFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 16711935
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Set c = SearchRange.Find(What:="", After:=SearchRange.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
If Not c Is Nothing Then
firstAddress = c.Address
Set foundrange = c
Do
Set c = SearchRange.Find(What:="", After:=c, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)

Set foundrange = Union(foundrange, c)
Loop While Not c Is Nothing And c.Address <> firstAddress
foundrange.Activate
sthfound = True
MsgBox "Purple fields found: " & foundrange.Count


End If
End If

If sthfound = False Then MsgBox "No purple field found"
End Sub

[Pawel]

Help anyone?

[Kenneth Hobs]

Try just doing one sheet. If that works, do a loop of the sheet array. Chip's FindAll() can be handy. http://www.cpearson.com/Excel/FindAll.aspx

I am not sure what you expect by searching for "".

Maybe attach a file and show what you expect to "find".

[Pawel]

I just need to find cells containing certain color. It doesn't matter what value is inside cell (just color matters)
The code below works just fine but only within active worksheet. When I try to change it to search this colored cell in certain sheets in active workbook (e.g. "aaa", "bbb", "ccc" ) it just doesn't work.


Dim cell As Range
Dim SearchRange As Range
Dim c As Range
Dim shtfound As Boolean
With ActiveWorkbook
If .ProtectWindows Or .ProtectStructure Then
MsgBox "Unlock workbook first"
Exit Sub
Else
sthfound = False
On Error Resume Next
Set SearchRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not SearchRange Is Nothing Then
With Application.FindFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 16711935
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Set c = SearchRange.Find(What:="", After:=SearchRange.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
If Not c Is Nothing Then
firstAddress = c.Address
Set foundrange = c
Do
Set c = SearchRange.Find(What:="", After:=c, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)

Set foundrange = Union(foundrange, c)
Loop While Not c Is Nothing And c.Address <> firstAddress
foundrange.Activate
sthfound = True
MsgBox " Purple fields found in this Tab: " & foundrange.Count


End If
End If
End If
End With
If sthfound = False Then MsgBox "No Purple field found"
End Sub


p.s. I've already checked the site you posted link to - I saw in in one of your earlier posts and was trying to solve my problem using it, but once again nothing useful comes to my mind:(

[Kenneth Hobs]

Keep in mind that an interior color is not equal to a conditional format's "interior color". Chip has a routine that does that but it is not fool-proof. The better way to go in those cases is to just look for the condition but that can be tedious if you have many conditions.

[Pawel]

Macro finds this color though. The only thing I can't figure out is how to perform search not on activesheet, but only on sheets "aaa", "bbb" and "ccc".

[Kenneth Hobs]

Can you post a simple example file?

[Aussiebear]

Maybe try something like this


Sub loopthrusheetarray()
Dim Sh As Worksheet
For Each Sh In Sheets(Array("AAA", "BBB", "CCC"))
'Your Code to do something....
Next Sh
End Sub

[Pawel]

@Aussiebear

Check out the first message (code) I've posted in this thread. I've tried something similar to your suggestion :

.
.
.
Set SearchRange = ThisWorkbook.Worksheets(Array("bbb", "aaa", "ccc")).UsedRange.SpecialCells(xlCellTypeVisible)
.
.
.
But it doesn't work:( If you could please clarify where and how would you put your suggestion to my code?

[Pawel]

Can you post a simple example file?
I wish I could, but the files that I'm working with are confidential ... even layouts :(

[Kenneth Hobs]

Set SearchRange = sh.UsedRange.SpecialCells(xlCellTypeVisible)

[Pawel]

Set SearchRange = sh.UsedRange.SpecialCells(xlCellTypeVisible)
This still doesn't specify sheets named : "aaa", "bbb" and "ccc" :(

[Paul_Hossler]

I think these are typos

I always use Option Explicit at the top of a module to catch things like this

16881

[Paul_Hossler]

Anyway, I'm unclear about what you want to do, but this will search the list of worksheets and find the first purple cell, activate that sheet and select that cell





Option Explicit

Sub test()
Dim v As Variant
Dim cell As Range
Dim SearchRange As Range, foundRange As Range
Dim c As Range
Dim firstAddress As String
Dim Msg As String

If ActiveWorkbook.ProtectWindows Or ActiveWorkbook.ProtectStructure Then
MsgBox "Unlock workbook first"
Exit Sub
End If

Worksheets("aaa").Select '---------- just for test

Msg = "No Purple field found"

For Each v In Array("aaa", "bbb", "ccc")
With Worksheets(v)

On Error Resume Next
Set SearchRange = .UsedRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If SearchRange Is Nothing Then GoTo NextSheet

With Application.FindFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 16711935
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Set c = SearchRange.Find(What:="", After:=SearchRange.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=True)

If c Is Nothing Then GoTo NextSheet

c.Parent.Select
c.Select

Msg = "Purple field found on " & c.Parent.Name & " in cell " & c.Address

Exit For
End With
NextSheet:
Next

MsgBox Msg
End Sub

[Kenneth Hobs]

Right, it specifies the sheet object in the loop that Aussiebear showed you which is what I tried to explain to you. If you want the sheet name while in the loop:

MsgBox sh.Name

Just combine posts (8 or 1) and 4 and you will have your solution. I guess that I could do it for you if it comes down to it. It is so simple, I did not do it yet as I would need to make a simple file to test just to be sure as I usually do. Just put your Dims above the loop and inside the loop you would change your ActiveSheet to SH. Any selection of a worksheet is likely not needed.

So, by not posting the simple workbook, getting help takes that much longer, sometimes. I seldom post untested solutions. How do I test? I make a simple workbook if I am not provided one to isolate the problem. That is how I solve problems. Basically, it helps us help you. Users that aren't willing to do a bit of work are less likely to get on-target help sometimes. Besides which, it teaches you how to help yourself for other problems as well.

[Pawel]

Kenneth and Paul - thanks so much for explaining it to me :) now I get it :)
I'm still a VBA rookie , that's why I needed this step by step instructions from you guys:)
Thanks again.