Finding a date in a sheet

  • Thread starter Thread starter serge
  • Start date Start date
S

serge

I'a using this formula to find a date in a sheet:

Set vRng = Blad2.Range("Rapport_Week").Cells.Find(What:=iMonday
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If iMonday = 01/01/2004, i get as a result cells with the dat
01/11/2004.

What's wrong in the formula
 
Taking a particularly tangential approach to Fermat's last theorem
I'a using this formula to find a date in a sheet:

Set vRng = Blad2.Range("Rapport_Week").Cells.Find(What:=iMonday,
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If iMonday = 01/01/2004, i get as a result cells with the date
01/11/2004.

What's wrong in the formula?
Click to expand...

Hi Serge,

Excel wants to do a search based on the text value, so change

What:=iMonday
to
What:=Format(iMonday, "dd/mm/yyyy")

and all will be rock'n'roll once more.

--
pjk

"If I have not seen as far as others, it is because giants were
standing on my shoulders."
-- Hal Abelson
 
Serge,

Just in case the music doesn't start for you,

unless the cells contain strings

Lookin:=xlformulas

wouldn't seem to be consistent with looking for strings. The usual advice
is to look for the dateserial.

What:=clng(cDate(iMonday))

but there are a lot of unknowns in what iMonday actually contains and what
is stored in the cells (dates with times for instance). I have found Find
to be inconsistent in finding dates, although I have never had it find the
wrong date. If the lookup range is a single column, you might use

res = Application.Match(clng(cDate(iMonday)),Blad2.Range("Rapport_Week"),0)
if not iserror(res) then
set rng = Blad2.Range("Rapport_Week")(res)
msgbox iMonday & " found at " & rng.Address
End if

Although the same cautions with what is contained in the range
Blad2.Range("Rapport_Week") and what iMonday contains.
 
Back
Top