Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Dates MM/YY only
My input box prompts for MM/YY and then I search for that date in column A.
The dates in column A are in MMMM YYYY format and the actual date is always the first of that month. When I tried to search using the results of the input box I got nothing - because when I type in 10/07 Excel thinks I mean 10/07/2007. So I had to convert the input to a valid date and make sure it was the first of the month. Here's my code for the whole thing. Is there a better way for me to do this? ********************** Private Sub Workbook_Open() Dim EnterDate As String Dim FindDate As Date Dim GoHere As Range ' Prompt user for month and year EnterDate = InputBox( _ "Enter the Month and Year for the data" & _ " being entered (mm/yy)", _ "Enter MM/YY format", Month(Now()) - 1 & "/" & Right(Year(Now()), 2)) ' If date entered is not valid display error msg then display input box again Do While Not IsDate(EnterDate) And EnterDate < "" MsgBox "The value entered is not a date. Please try again." EnterDate = InputBox("Enter the Month and Year for the data " & _ "being entered (mm/yyyy)", "Enter MM/YYYY format", _ Month(Now()) - 1 & "/" & Right(Year(Now()), 2)) Loop ' Change date to use for searching to be the first of the month entered If EnterDate = "" Then MsgBox "User Cancelled" Else FindDate = Left(EnterDate, InStr(1, EnterDate, "/") - 1) & "/1/" & Right(EnterDate, 2) ' Go look in column A for the matching date, move to that cell the move right two cells Set rng = Columns(1).Cells res = Application.Match(CLng(FindDate), rng, 0) If Not IsError(res) Then Set GoHere = rng(res) GoHere.Select ActiveCell.Offset(0, 2).Select Else MsgBox FindDate & " not found" End If End If Exit Sub addError: MsgBox "This is not a date." Resume End Sub ******************** As always I greatly appreciate the help I get here!! Lauri S. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Dates MM/YY only
On Sep 4, 5:12 pm, LauriS wrote:
My input box prompts for MM/YY and then I search for that date in column A. The dates in column A are in MMMM YYYY format and the actual date is always the first of that month. When I tried to search using the results of the input box I got nothing - because when I type in 10/07 Excel thinks I mean 10/07/2007. So I had to convert the input to a valid date and make sure it was the first of the month. Here's my code for the whole thing. Is there a better way for me to do this? ********************** Private Sub Workbook_Open() Dim EnterDate As String Dim FindDate As Date Dim GoHere As Range ' Prompt user for month and year EnterDate = InputBox( _ "Enter the Month and Year for the data" & _ " being entered (mm/yy)", _ "Enter MM/YY format", Month(Now()) - 1 & "/" & Right(Year(Now()), 2)) ' If date entered is not valid display error msg then display input box again Do While Not IsDate(EnterDate) And EnterDate < "" MsgBox "The value entered is not a date. Please try again." EnterDate = InputBox("Enter the Month and Year for the data " & _ "being entered (mm/yyyy)", "Enter MM/YYYY format", _ Month(Now()) - 1 & "/" & Right(Year(Now()), 2)) Loop ' Change date to use for searching to be the first of the month entered If EnterDate = "" Then MsgBox "User Cancelled" Else FindDate = Left(EnterDate, InStr(1, EnterDate, "/") - 1) & "/1/" & Right(EnterDate, 2) ' Go look in column A for the matching date, move to that cell the move right two cells Set rng = Columns(1).Cells res = Application.Match(CLng(FindDate), rng, 0) If Not IsError(res) Then Set GoHere = rng(res) GoHere.Select ActiveCell.Offset(0, 2).Select Else MsgBox FindDate & " not found" End If End If Exit Sub addError: MsgBox "This is not a date." Resume End Sub ******************** As always I greatly appreciate the help I get here!! Lauri S. Phillip UK London This works for me Sub testdate() Dim EnterDate As Variant Dim FindDate As Date Dim OKDate As Boolean OKDate = False Do While Not OKDate EnterDate = _ InputBox("Enter the Month and Year to search in mm/yyyy format", _ "Enter Data", _ Month(Now()) - 1 & "/" & Right(Year(Now()), 4)) If EnterDate = "" Then MsgBox "User Cancelled" Exit Do ElseIf Not IsDate(EnterDate) Then MsgBox "The value entered is not a date. Please try again." Else OKDate = True End If Loop ' Change user date to the first of the month If OKDate Then FindDate = "1/" & EnterDate ' look in column A for the matching date, select two cells to right from matching date Set rng = Columns(1).Cells res = Application.Match(CLng(FindDate), rng, 0) If Not IsError(res) Then rng.Cells(1).Offset(res - 1, 2).Select Else MsgBox FindDate & " not found" End If End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Dates MM/YY only
Hi Lauri,
Your approach seems fine though you could have a look at this too, works if user enters m/yy or d/m/yy (US) with or without leading zeros. Sub test() Dim lngDate As Long Dim sInput As String Dim va Dim res As Long Dim rng As Range With Range("A7") ..Value = DateSerial(7, 10, 1) ..NumberFormatLocal = "mmmm yyyy" End With sInput = "10/07" 'sInput = "10/1/07" ' US m/d/y 'or international 'sInput = "01/10/07" ' d/m/yy va = Split(sInput, "/") If UBound(va) = 2 Then ' user entered */*/* lngDate = CDate(sInput) ElseIf UBound(va) = 1 Then lngDate = DateSerial(va(1), va(0), 1) Else ' user input error End If ' handle non valid date 'out of reasonable bounds', eg If lngDate < DateSerial(1980, 1, 1) Or lngDate DateSerial(2040, 1, 1) Then ' non valid date Exit Sub End If Set rng = Columns(1).Cells res = Application.Match(lngDate, rng, 0) ' potential error here if not found Debug.Print CDate(lngDate) Debug.Print res End Sub Obviously this is trimmed from your routine, and would benefit from adequate error handling. I like the Match approach to finding the date in cells. Normally would use the Find function, which has some advantages but dates can be a bit odd and require a particular non-intuitive format in the 'what' argument for non US date users. Regards, Peter T "LauriS" wrote in message ... My input box prompts for MM/YY and then I search for that date in column A. The dates in column A are in MMMM YYYY format and the actual date is always the first of that month. When I tried to search using the results of the input box I got nothing - because when I type in 10/07 Excel thinks I mean 10/07/2007. So I had to convert the input to a valid date and make sure it was the first of the month. Here's my code for the whole thing. Is there a better way for me to do this? ********************** Private Sub Workbook_Open() Dim EnterDate As String Dim FindDate As Date Dim GoHere As Range ' Prompt user for month and year EnterDate = InputBox( _ "Enter the Month and Year for the data" & _ " being entered (mm/yy)", _ "Enter MM/YY format", Month(Now()) - 1 & "/" & Right(Year(Now()), 2)) ' If date entered is not valid display error msg then display input box again Do While Not IsDate(EnterDate) And EnterDate < "" MsgBox "The value entered is not a date. Please try again." EnterDate = InputBox("Enter the Month and Year for the data " & _ "being entered (mm/yyyy)", "Enter MM/YYYY format", _ Month(Now()) - 1 & "/" & Right(Year(Now()), 2)) Loop ' Change date to use for searching to be the first of the month entered If EnterDate = "" Then MsgBox "User Cancelled" Else FindDate = Left(EnterDate, InStr(1, EnterDate, "/") - 1) & "/1/" & Right(EnterDate, 2) ' Go look in column A for the matching date, move to that cell the move right two cells Set rng = Columns(1).Cells res = Application.Match(CLng(FindDate), rng, 0) If Not IsError(res) Then Set GoHere = rng(res) GoHere.Select ActiveCell.Offset(0, 2).Select Else MsgBox FindDate & " not found" End If End If Exit Sub addError: MsgBox "This is not a date." Resume End Sub ******************** As always I greatly appreciate the help I get here!! Lauri S. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Dates MM/YY only
typo -
works if user enters m/yy or d/m/yy (US) with or without leading zeros. My date format is International, not US, but I did adjust to cater if US user enters m/d/yy Peter T "Peter T" <peter_t@discussions wrote in message ... Your approach seems fine though you could have a look at this too, works if user enters m/yy or d/m/yy (US) with or without leading zeros. <snip |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Dates MM/YY only
Thanks, Peter!! I'm very new to programming in Excel and didn't know about
Split, UBound and DateSerial. That Split will come in very handy!! I like the Match approach to finding the date in cells. I learned that from Tom Ogilvy. Lauri S. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Dates MM/YY only
I like the Match approach to finding the date in cells.
I learned that from Tom Ogilvy. I might have guessed <g Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering dates | Excel Discussion (Misc queries) | |||
Entering Dates | Charts and Charting in Excel | |||
Entering Dates | Excel Discussion (Misc queries) | |||
entering dates | Excel Discussion (Misc queries) | |||
Entering dates | Excel Discussion (Misc queries) |