Input box to find date

[Tom]

Hi,

As part of my macro, how can I make an input box (or whatever box) to
pop up and ask the user to input a date - e.g. 25/06/2097? On
clicking OK, it then goes and locate the date in the spreadsheet.
Thanks for your help.

Tom

 

[Roger Govier]

Hi Tom

Jacob Skaria posted a solution to another query in the programming group
a short while ago.
It should also suit your needs
Sub Macro2()

Dim varDate As Variant, varFound As Variant

varDate = InputBox("Enter Date to be searched")

If IsDate(varDate) Then
Set varFound = Columns(1).Find(CDate(varDate), _
LookIn:=xlValues, Lookat:=xlWhole)
If Not varFound Is Nothing Then
varFound.Activate
Else
MsgBox "Date not found"
End If
Else
MsgBox "Invalid Date"
End If
End Sub

-- Jacob (MVP - Excel)

 

[Jacob Skaria]

Try the below


Sub Macro2()

Dim varDate As Variant, varFound As Variant

varDate = InputBox("Enter Date to be searched")

If IsDate(varDate) Then
Set varFound = Cells.Find(CDate(varDate), _
LookIn:=xlValues, Lookat:=xlWhole)
If Not varFound Is Nothing Then
varFound.Activate
Else
MsgBox "Date not found"
End If
Else
MsgBox "Invalid Date"
End If
End Sub

 

[Tom]

Thanks Roger for pointing that out.

Hi Tom

Jacob Skaria posted a solution to another query in the programming group a
short while ago.
It should also suit your needs
Sub Macro2()

Dim varDate As Variant, varFound As Variant

varDate = InputBox("Enter Date to be searched")

If IsDate(varDate) Then
Set varFound = Columns(1).Find(CDate(varDate), _
LookIn:=xlValues, Lookat:=xlWhole)
If Not varFound Is Nothing Then
varFound.Activate
Else
MsgBox "Date not found"
End If
Else
MsgBox "Invalid Date"
End If
End Sub

-- Jacob (MVP - Excel)

 

[Tom]

That does exactly what I want. Thanks a lot jacob.