Input box to find date

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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)
 
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
 
Thanks Roger for pointing that out.

Roger Govier said:
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)
Click to expand...
 
Back
Top