[vba] exact value search

Thank you very much!

The argument lookat:=xlWhole indeed allows you to search for an exact value,
as opposed to lookat:=xlPart which allows searching for a value contained within the cell or sheet, by default in the absence of an argument.

Unfortunately, it still does not search for the exact value, but for the contained value.

This small program proves it:

"Public Sub test()

Dim number As Integer
Dim foundCell As Range
Dim row As Integer
Dim col As Integer

number = 8

Set foundCell = Range("A1:A5").Find(number)

row = foundCell.Row
col = foundCell.Column

MsgBox ("found: row = " & row & " , column = " & col)

End Sub
"

Because it returns the coordinates of a cell containing the number 48, while I am searching for the number 8!

Hello,

In an Excel sheet, I'm trying to select the cell containing today's date by clicking on a CommandBox:

Here's the code I wrote:

Code:

Private Sub cmd_today_Click()

Dim today As Date
Dim foundCell As Range
Dim row As Integer
Dim col As Integer

today = Date

Set foundCell = Range("D3:HE3").Find(today, lookat:=xlWhole)

If foundCell Is Nothing Then
MsgBox ("not found " + Chr(13) + Str(Date))
Else
row = foundCell.Row
col = foundCell.Column
MsgBox ("found: row = " & row & ", column = " & col)
foundCell.Select
End If

End Sub

However, the function used isn't finding any cell (even though, of course, there is a cell that contains today's date...)
On the other hand, this cell is a formula (=left cell +1)

Thanks for your contributions'

@+

When you use the find method among formulas, you need to specify whether you want to compare your string with the formulas or with their results:

Try using find(today, lookin:= xlValues, lookat:= xlWhole)

Hello cel92,

You just need to remove the part "LookIn:=xlValues" from your code.