Moving down one cell
 

Here is what I use. It converts the entire selected range (to a maximum of
the used range). I keep this in an addin which is accessed via a menu bar.

Private Const m_MaxYear As Integer = 2099
Private Const m_MinYear As Integer = 1970

Private Sub Convert()
On Error GoTo ErrorHandler
Dim rngCurrent As Range
Dim rngToSearch As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) < "=" Then
If Len(rngCurrent) = 8 And ValidMonth(rngCurrent) And
ValidYear(rngCurrent) Then
rngCurrent.NumberFormat = "mm/dd/yy"
rngCurrent.Value = CDate(rngCurrent.Value)
End If

End If
Next

End If

ErrorHandler:
Application.Calculation = xlCalculationAutomatic
End Sub

Private Function ValidMonth(ByVal rngCurrent As Range) As Boolean
Dim blnReturnValue As Boolean

blnReturnValue = False
Select Case UCase(Left(rngCurrent.Value, 3))
Case "JAN"
blnReturnValue = True
Case "FEB"
blnReturnValue = True
Case "MAR"
blnReturnValue = True
Case "APR"
blnReturnValue = True
Case "MAY"
blnReturnValue = True
Case "JUN"
blnReturnValue = True
Case "JUL"
blnReturnValue = True
Case "AUG"
blnReturnValue = True
Case "SEP"
blnReturnValue = True
Case "OCT"
blnReturnValue = True
Case "NOV"
blnReturnValue = True
Case "DEC"
blnReturnValue = True
End Select

ValidMonth = blnReturnValue

End Function

Private Function ValidYear(ByVal rngCurrent As Range) As Boolean
Dim blnReturnValue As Boolean
Dim intYear As Integer

If IsNumeric(Right(rngCurrent.Value, 4)) Then
intYear = CInt(Right(rngCurrent.Value, 4))
If intYear m_MinYear And intYear < m_MaxYear Then
blnReturnValue = True
Else
blnReturnValue = False
End If
Else
blnReturnValue = False
End If

ValidYear = blnReturnValue

End Function
--
HTH...

Jim Thomlinson


"Jordan" wrote:

I have a date listed as APR 2005 or Mar 2005. If I format the column as a
date it doesnt change until I press F2 and hit enter and then it shows as
04/01/2005 or 03/01/2005. Which is what I want.

Can anyone tell me how what code to use to tell it to press F2, hit enter
and then move down one cell until it reaches a blank cell.

Or is there a way to re-calculate all the cells without having to open them?

Thanks in advance for any help you can give me.