Qn: How to show a date and time with a ' in it???

[Michael Vaughan]

Hello Everyone,

I am trying to setup a page so that I can import a excel file to my
Microsoft Outlook Calendar. I can do it manually, but I am trying to write
a routine that will format the page for me. The problem is the time and
date. I have a routine that Tom Olgivy wrote for me:

Dim cell As Range
For Each cell In Range("A1:F10")
cell.Value = "'" & cell.Value
Next

It works for everything except the date and time. The problem is, when it
see's the time with a ', it treats it as TEXT. Now, if I manually put in a
' before the time in the formula bar, it will take it??? Then the time can
be imported correctly. But, how can I input that little tick ' in a routine
to do it automatically without it recognizing it as text???

Thanks.. mv

 

[Bernie Deitrick]

Michael,

You need to use a custom function to check for time values - see the code
below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range
For Each myCell In Range("A1:F10")
If IsTime(myCell) Or IsDate(myCell) Then
myCell.Value = "'" & myCell.Text
Else
myCell.Value = "'" & myCell.Value
End If
Next
End Sub

Public Function IsTime(myCell As Range) As Boolean
IsTime = True
With myCell
IsTime = IsTime And IsNumeric(.Value) And _
InStr(.NumberFormat, "h:m")
End With
End Function

 

[Bernie Deitrick]

I did a bad job of trimming J.E. McGimpsey's code to handle only one cell
rather than a multi-area range. It should be:

Public Function IsTime(myCell As Range) As Boolean
With myCell
IsTime = IsNumeric(.Value) And _
InStr(.NumberFormat, "h:m")
End With
End Function

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Couldn't wait?