Thread: Find function doesn't work with reference and non-visible text in cell (Excel2007)

Dear Excel users,

I would like to use the find function, but it doesn't work properly just yet.

My wishes are:
* The cell that has to be found contains a reference to another cell (e.g. =L3).
* The cell that has to be found has a width of 0,1. So the text is not visible in the cell.

With LookIn:=xlFormulas the cell with a reference isn't found, the cell with the original value is found also with a width of the cell of 0,1.

With LookIn:=xlValues both cells are found, but none of the cells are found when the width is 0,1.

A concised example is attached. The macro is given in the following quote.

Sub ProFindCell()
Dim findCell As Double

'findCell is the value to be found in cell B3
findCell = Range("B3").Value

'CDate is used to convert findCell in a date format
Cells.Find(What:=CDate(findCell), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

MsgBox ActiveCell.Column 'Eventually I would like to have the column number of the found cell.
Exit Sub

End Sub

Hopefully someone has a suggestion?

Thanks! Wiggert

Hello Wiggert,

Try this...

Sub ProFindCell()
Dim findCell As Double

'findCell is the value to be found in cell B3
findCell = Range("B3").Value

'CDate is used to convert findCell in a date format
Cells.Find(What:=CDate("*" & findCell.Text & "*"), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

MsgBox ActiveCell.Column 'Eventually I would like to have the column number of the found cell.
Exit Sub

End Sub

Hello Wiggert,

The asterisk is used as a "wildcard" character in the Find function. It means match zero or more characters.

This piece of code:

  findCell.Text

retrieves the cell text, the formatted part that you see on the worksheet.

This line

  CDate("*" & findCell & "*")

is generating error because I used the wrong syntax. The wildcard characters are not legal date characters. Change it to this...

  "*" & findCell & "*"

Hello Wiggert,

It would easier for me to troubleshoot the problem using the original data. Can you post your workbook? I am using Excel 2003.

Hello Wiggert,

Excel 2003 can read 2007 files with some upgrades, but I haven't gotten them yet. The xls file I can read. Thanks for posting it.

Hello Wiggert,

Here is what I have found. The Find function will miss cells if the data can not be displayed in full. Both xlFormulas and xlValues settings are affected the same way. So, I need to develop a "Plan B" using VBA to locate thiese cells in a fast and efficient manner, regardless of the cell size or if they are not visible.