Deny in "Find" based on flag

[Guest]

Try to visualize a WB with 32 WS. They consist of 4 years' worth of info each
in a set of 8 WS, being 2 WS for each quarter. (This is payroll data).

The information I am interested in has a code that could be one of nine
possible three-letter sets:

ABC, DEF, GHI, JKL

I need to extract the LATEST data for each code only once.

My plan is to start at the latest quarter (Q4, page2), find the person, and
once found (which might involve stepping back to page1), add up all the data
for the codes THAT I FIND. As I find each one, I need to trigger a flag that
says "ignore this 'code' as we go through the rest of the quarters and
pages", and then step back to the next quarter and repeat the "Find" for any
data I didn't catch so far.

I will have the WS names in an array. Is the way to do this to build a
two-dimensional array with the codes in one column, and a Boolean flag in the
other? Once the code is used, set the flag to False, and continue until all
the flags are False, then leave the whole search process, and reset all the
flags to True for the next search candidate.

I have the picture fairly clearly in my mind, and I have had the code
working previously where I only had to look for the codes in a single WS, and
this did involve stepping through the array of WS to find the dude that did
the dirty deed.

Looking for some guidance on this -- I think I'm getting close to the "deep
end" <g>!

 

[Guest]

How much grief would it cause if you copied the data to new sheets and sorted
it so you didn't have to set the flags. You could just go down the column of
each sheet, repeating for each name.

 

[Guest]

The reason for 2 pages is that the first page in each quarter nips the 65K
Rows boundary -- too many employees <g>! In any given uarter a single
employee could have data on as many as 60 Rows, so I might have only a
thousand or so employees on a single WS, but still be bumping that boundary.

 

[Guest]

FWIW, this doesn't compile:

Dim strCodes As String
Dim blnReg As Boolean, blnOt As Boolean,...etc.

Set strCodes = Array("{""REG"", blnReg; _
""OT-"", blnOt; _
""CSL"", blnCsl; _
""HOL"", blnHol; _
""ROT"", blnRot; _
""HLD"", blnHld; _
""SBN"", blnSbn; _
""PRF"", blnPrf; _
""RPY"", blnRpy}")

 

[Guest]

Perhaps this is a better approach:

Dim varCodes As Variant
Dim blnReg As Boolean, _
blnOt As Boolean, _
blnCsl As Boolean, _
blnHol As Boolean, _
blnRot As Boolean, _
blnHld As Boolean, _
blnSbn As Boolean, _
blnPrf As Boolean, _
blnRpy As Boolean, _
blnCodes As Boolean
Dim intI As Integer
Dim strSearchList As String

Set varCodes = Array(blnReg, _
blnOt, _
blnCsl, _
blnHol, _
blnRot, _
blnHld, _
blnSbn, _
blnPrf, _
blnRpy)

strSearchList = ""
For intI = 1 To 9
' If the Flag blnReg is True, Then
' strSearchList = strSearchList + "REG"
' End If
' If the Flag blnCsl is True, Then
' strSearchList = strSearchList + "CSL"
' End If
' Etc.......
Next intI

???

 

[Guest]

Tweak that For Loop ...

For intI = 1 To 9
' If the Flag blnReg is True, Then
' strSearchList = strSearchList + "REG"
' End If
' If the Flag blnCsl is True, Then
' If IsEmpty(strSearchList)
' strSearchList = strSearchList + "CSL"
' Else
' strSearchList = strSearchList + ", CSL"
' End If
' End If
' Etc.......
Next intI

 

[Guest]

Dim blnCodes(1 to 9) as Boolean
Dim Codes(1 to 9) as String
Dim Dates(1 to 9) as Date
Dim intI As Integer
Dim shList as Variant
shlist = Array("sh1", "sh2", . . . , "sh31", "sh32")
Codes(1) = "ABC"
Codes(2) = "EFG"
.. . .
Codes(9) = "TUV"
for shIndex = Ubound(shList) - 1 to Lbound(shList) step -2
For intI = lbound(blncodes) to ubound(blncodes)
if blncodes(intI) Then
search shIndex
search shIndex + 1
determine the latest date for this quarter
update blncodes(intI)
end if
Next intI
Next shIndex

 

[Guest]

Ten minutes more, and I'm outta here 'til Tuesday, but that looks like
exactly what I was looking for. No wonder your an "MVP"! Thanks.

 

[Guest]

Terrific stuff, Tom. It took me almost an hour to understand it, but now I
do, I have been able to hack it as needed. Now I need to do something
additional: As I am stepping backwards through 31 Sheets, turning off the
boolean for the various codes that I am seeking as I go, once I have turned
off the last boolean, there is no further need to step through the Sheets.
So...

Dim blnCodes(1 To 9) As Boolean 'This guy's up at the top of things already
If Not blnCodes(1) And Not blnCodes(2) ... and Not blnCodes(9) Then
Exit For
End If

...but thar seems awfully clumsy. Is there some other way to do it that is
more elegant? I thought of this:

Dim blnCodes(1 To 9) As Boolean 'This guy's up at the top of things already
Dim blnCheck As Boolean
Dim intK as Integer
blnCheck = False

'Big For loop of pages is running
For intK = 1 To 9
If blnCodes(intK) Then
blnCheck = True
Exit For
End If
Next intK
If Not blnCheck Then
Exit For 'The big guy
End If

...or is there some way that is cleaner?