Cells.Find and text in hiddend column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel Experts,

At the end of a number of modules in my code, I find specific text, and in
the cell to the right, enter the text "DONE". An example would be:

Cells.Find(What:="Save GS Files").Offset(, 1).Select
Selection.Value = "DONE"

Generally, I hide the column that contains the text that the cells.find
looks for. Generally this doesn't cause a problem, but sometimes this part
of the code fails, and to get it to work I have to unhide the column that
hold the text.

Is this a known problem where a cells.find command can't find text in a
hidden cell or is this an unusually/bug type situation? Or should I be
adding some additional parameters to the cells.find command?

Thanks,
Alan
 
Excel cannot select a hidden cell or worksheet. Just don't select it.

Cells.Find(What:="Save GS Files").Offset(, 1).Value = "DONE"
 
I have had a different experience.
In my experience if you use the parameter
Lookin:=xlFormulas
it finds the hidden cell.

Lookin:=xlValue
it doesn't

That would be consistent with your experience since you don't explicitly set
it and this is one of the persistent settings.

I wouldn't discount Dave Peterson's experience, but then again he may have
never really studied the problem.
 
After playing with it a little, I think you are right, I just assumed he was
selecting the hidden cell (which he obviously is not - I got ahead of myself).

Thanks Tom!
 
I do recall that I was surprised when my .finds didn't .find!

But I don't recall trying formulas/values.

I'll defer to you (and test it quietly later <vbg>).

==
But looking for "Tom Ogilvy" may cause a problem if you really have formulas
like:

="Tom" & " " & "Ogilvy"

In hidden cells.

(Back from my quiet testing. xl2003 works the way you said. (I bet you're not
surprised!))

Time to update my memory (ouch, that hurts!)
 
To all who responded:

Thanks very much for the help. I'll try the solutions suggestions.

Alan
 
Hi - I had the same problem in a row, and I was NOT selecting the cell.
Values just don't work, and I can't use .formula in my app.

I tried the simple loop below, and it 'found' the value in a hidden row, go
figure?

for Row = 1 to 10
if cells(row,2).value = "what you're looking for" then exit for
next row
' if row is > 10 here, then the value was not found.
 
Back
Top