Help - Need a Macro/Popup to Find a Date in a Sheet.
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. |
Help - Need a Macro/Popup to Find a Date in a Sheet.
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 -- Jacob (MVP - Excel) "Kev - Radio Man" wrote: 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. |
Help - Need a Macro/Popup to Find a Date in a Sheet.
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. -- Regards, OssieMac "Kev - Radio Man" wrote: 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. |
Help - Need a Macro/Popup to Find a Date in a Sheet.
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. |
Help - Need a Macro/Popup to Find a Date in a Sheet.
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 -- Jacob (MVP - Excel) "Kev - Radio Man" wrote: 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. |
Help - Need a Macro/Popup to Find a Date in a Sheet.
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? "OssieMac" wrote: 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. -- Regards, OssieMac |
Help - Need a Macro/Popup to Find a Date in a Sheet.
Jacob,
Thank You, that did the job. It'll make life easier. Kevin. "Jacob Skaria" wrote: 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 -- Jacob (MVP - Excel) "Kev - Radio Man" wrote: 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. |
All times are GMT +1. The time now is 04:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com