Using Find Method to match dates

[Astroboy142]

I am trying to use the .Find method to match a date. In sheet2 I have a range of cells which are formatted to only show the day "dd" (I could format each cell to show (dd-mmm-yy, etc). In sheet1, I have dates which are formatted as "January 7, 2009"

I want to create a VBA find routine which allows me to match the date from Sheet2 (Ranged between cells A1:Z1) with Sheet1. Then output a MsgBox stating "Match found in cell $A$7 of Sheet1"

I have tried a formula similar to:

Set FoundCell = Sheets("Sheet1").Range("A1:Z1").Find _
   (what:=DateValue("January 7, 2009") ,lookin:=xlFormulas) 

Any suggestions?

[pike]

Hi Astroboy142
try...

Sub ptest()
searchstring = "January 7, 2009" 
Set LookInR = Sheets("Sheet1").Range("A1:Z1")
With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
               msgbow "Match found; " & fAddress
              Loop While FoundOne.Address <> fAddress
        End If
End With
End Sub

[pike]

Dude how many dupilcate post do you have?

[Astroboy142]

personally, I don't think I have any double post

[pike]

Ok , dont know what I was think
thanks for marking the post solved
It refeshing to someone having a real go at the code before asking questions
looks like your project is developing nicely

[royUK]

A date in Excel is ctully a number (Long). When using .Find with dates you may get a problem searching for the date as a string. Try using

Option Explicit

Sub ptest()
    searchstring = "January 7, 2009"
    Set LookInR = Sheets("Sheet1").Range("A1:Z1")
    With LookInR
        Set FoundOne = .Find(What:=DateValue(searchstring), lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
                msgbow "Match found; " & fAddress
            Loop While FoundOne.Address <> fAddress
        End If
    End With
End Sub

If looking for multiple dates it might be faster using AutoFilter