Finding every instance of a value in a multi-sheet workbook
 

I need to find every occurrence of a unique ID in a multi-sheet
workbook.

I have already implemented this in code simply by looping over the
sheets, finding the column (it can potentially move from sheet to
sheet, but doesn't yet), and then looping over that column to find the
row numbers and put them in an array.

But now I'm thinking that this might not be the best way to do this,
and some sort of Find would be more efficient. I've never seen
anything like this though, a variation of Find that returns a single
"something" with all of the hits in it.

Any ideas?

Maury

Finding every instance of a value in a multi-sheet workbook
 

That's probably as good a method as any. Someone may have a better way.

"Maury Markowitz" wrote:

I need to find every occurrence of a unique ID in a multi-sheet
workbook.

I have already implemented this in code simply by looping over the
sheets, finding the column (it can potentially move from sheet to
sheet, but doesn't yet), and then looping over that column to find the
row numbers and put them in an array.

But now I'm thinking that this might not be the best way to do this,
and some sort of Find would be more efficient. I've never seen
anything like this though, a variation of Find that returns a single
"something" with all of the hits in it.

Any ideas?

Maury

Finding every instance of a value in a multi-sheet workbook
 

You can use Jan Karel Pieterse's FlexFind:
http://www.oaltd.co.uk/MVP/

If you have to have your own code, look at .findnext in VBA's help.

Maury Markowitz wrote:

I need to find every occurrence of a unique ID in a multi-sheet
workbook.

I have already implemented this in code simply by looping over the
sheets, finding the column (it can potentially move from sheet to
sheet, but doesn't yet), and then looping over that column to find the
row numbers and put them in an array.

But now I'm thinking that this might not be the best way to do this,
and some sort of Find would be more efficient. I've never seen
anything like this though, a variation of Find that returns a single
"something" with all of the hits in it.

Any ideas?

Maury

--

Dave Peterson

Finding every instance of a value in a multi-sheet workbook
 

On Jul 2, 8:46*pm, Dave Peterson wrote:
You can use Jan Karel Pieterse's FlexFind:http://www.oaltd.co.uk/MVP/

Close, but no cigar I'm afraid. From what I can see this is a UI-only
gizmo, no VBA that I can figure out, at least without looking at the
code. I have written to him.

Maury

Finding every instance of a value in a multi-sheet workbook
 

How about this suggestion??

If you have to have your own code, look at .findnext in VBA's help.

Maury Markowitz wrote:

On Jul 2, 8:46 pm, Dave Peterson wrote:
You can use Jan Karel Pieterse's FlexFind:http://www.oaltd.co.uk/MVP/

Close, but no cigar I'm afraid. From what I can see this is a UI-only
gizmo, no VBA that I can figure out, at least without looking at the
code. I have written to him.

Maury

--

Dave Peterson

Finding every instance of a value in a multi-sheet workbook
 

On Jul 3, 10:27*am, Dave Peterson wrote:
How about this suggestion??

If you have to have your own code, look at .findnext in VBA's help.

I did. However, MS, in their infinite wisdom, decided that .FindNext
will not look in hidden cells. The cells I'm looking for are, of
course, hidden. FindNext has all sorts of other problems too, I find
it to be generally unstable in terms of predictable behavior.

BTW, a text editor demonstrated that FlexFind also uses Find/FindNext.

Maury

Finding every instance of a value in a multi-sheet workbook
 

You can either unhide all the columns/rows or you can loop through the usedrange
of each sheet.

I don't think that there's any middle ground.

Maury Markowitz wrote:

On Jul 3, 10:27 am, Dave Peterson wrote:
How about this suggestion??

If you have to have your own code, look at .findnext in VBA's help.

I did. However, MS, in their infinite wisdom, decided that .FindNext
will not look in hidden cells. The cells I'm looking for are, of
course, hidden. FindNext has all sorts of other problems too, I find
it to be generally unstable in terms of predictable behavior.

BTW, a text editor demonstrated that FlexFind also uses Find/FindNext.

Maury

--

Dave Peterson