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