Match date in Excel .. Try 2

[HobbitK]

Hi everyone...
brand new to VBA ... just doing a test project.
With the assisstance of 2 wonderful people on here today,
I got this code... but it does not run. I get a Compile Error: Sub or Function not defined. it happens on fCell in the Set fCell line.Since I did not write the code, and do not yet know VBA constants and keywords, I am really lost
Here is the code.

Private Sub CommandButton1_Click()
Dim SearchDay As Date
Dim fCell As Range
SearchDay = InputBox("Enter date to find", "What day?&quot

With Sheets("Sheet1&quot
    set fCell = .Columns("A&quot.Find(Format(SearchDay, .Range("A2&quot.NumberFormat), LookIn:=xlValues)
If Not fCell Is Nothing Then
MsgBox "Date Found"

Else
MsgBox "Date Not Found"

End If
End Sub
The original posting of this code did not have fCell dimensioned. Someone told to Dim it as a Range. If I remark the Dim line out, then I get a Variable not defined error.
Any help and/or instructions?
Thanks
Michael

 

[Nikita6003]

Hi Michael

Is your code a direct copy? Because you've got a With statement bu no End With

Try this instead & see if it works:

    Dim SearchDay As Date
    Dim fCell As Range
    
    SearchDay = InputBox("Enter date to find", "What day?")
    
    With Sheets("Sheet1")
        Set fCell = .Columns("A").Find(Format(SearchDay, .Range("A2").NumberFormat), LookIn:=xlValues)
    End With
    If Not fCell Is Nothing Then
        MsgBox "Date Found"
    Else
        MsgBox "Date Not Found"
    End If

Cheers
Nikki

 

[xlbo]

D'oh - guess that's what happens when you just write "snippets"
Thanx for tidying yup 4 me Nikki ;-) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

Any time - this is holiday time so got time to spare b4 we leave for the snowy slopes of France!

N

 

[xlbo]

Cool - snowboard / ski / other ??? Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

ski + deteremined to try something like parapenting
unless i get scared, of course

 

[xlbo]

ok - I know this is waaaaaaay OT but what in the name of (insert amazed expression) is parapenting ?? Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

erm - jumping off a cliff wearing nothing but skis & a parachute-type thingy &a guy or gal to steer the damn thing (at least i *hope* the latter = included ...)

sounded like fun when i booked it, anyway

N

 

[xlbo]

LOL - sounds like much fun - enjoy yourself and be prepared to lose your spot in the VBA rankings ;-) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Loomah]

sounds cold to me?!?!?

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[HobbitK]

Geoff and Nikki ...
Thanks so much. You both are wonderful
Would someone please take a minute and explain this code, what it is doing and exactly why you wrote it this way.
I think it will help me understand more about VBA

Set fCell = .Columns("A&quot.Find(Format(SearchDay, .Range("A2&quot.NumberFormat), LookIn:=xlValues)

Nikki ... Enjoy your ski trip & parapenting. That sounds like FUN .. scary but fun.
Thanks again
Michael

 

[xlbo]

Ok - here goes:

This uses the FIND method
SearchDay is the variable that is holding the date (in its initial format). This is then formatted to the same "numberformat" as the 2nd cell in the column that you are searching in. This is to make sure that the dates are comparable and can be found

The FIND method is VERY picky about dates and any slight difference in the date format will mean that the date will not be found, even if it exists. Therefore, we are setting the format of the variable to be found as the same format as the cells you are trying to find it in

Alos - I used .Columns and .Range as they are within a WITH statement - this tends to make code run faster as all references are implicit

HTH Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[HobbitK]

Geoff ...
Thanks for the lesson. Much appreciated

Michael

 

[RobBroekhuis]

Michael,
How about a star for Geoff and/or Nikki?
Rob