Help - Need a Macro/Popup to Find a Date in a Sheet.

[Kev - Radio Man]

Hi,

I have a sheet with the date down in column A.
I made a macro up (thought I had) to find a date, click on the command
button, brings up a feild box to enter a date and then goto the date in
column A.
However it did not work.
I am using 2007 now so my dates go on and on now.

Is there any easy an dpresentable way to do this in code?

Thanks Kevin.

 

[Jacob Skaria]

Try th ebelow

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(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

 

[OssieMac]

Hi Kev,

Not sure that I understand correctly so lets try to confirm the spec for
what you require. Does the following describe what you require?

Steps:
1. User Clicks a command button that will then display an InputBox.
2. User enters a date in the InputBox.
3. System finds and selects the cell containing the date.
4. If the entered date is not found, what do you want the code to do?

If above is not correct then try to explain the steps you require using the
above format.

 

[Kev - Radio Man]

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(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,

Thanks for that, it does work, but my mistake, I meant to say that when the
date is entered it goes to that date (row) in that sheet if found, else
produce an error message.
Your code does do as it asks, says "Yes" the date exsists.

Sorry for my mis-explaination of what I was trying to do. Your help is most
grateful.

Kevin.

 

[Jacob Skaria]

Try this version..

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

 

[Kev - Radio Man]

OssieMac,

Thanks for your reply.
You are basicly correct, sorry for not explaining fully.

1. Button to clcik to open a data entry box.
2. Search through column A to locate the date, if present.
3. If the date is found, display the row data centre of the screen so I can
see the data above and below this row.

I use the sheet to log employee leave data, AL, sick etc. In some cases the
request is for months ahead, currently I have to scroll down to the requested
date.
I am trying to find a quicker way to get to the row I want to see.
Soemtimes I need to see dates prior to the current date if there is a
dispute with leave.

Hope this help explain my situation?

 

[Kev - Radio Man]

Jacob,

Thank You, that did the job. It'll make life easier.

Kevin.