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

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

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

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


"Michael Vaughan" wrote in message
...
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

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

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Michael Vaughan" wrote in message
...
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

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

Couldn't wait?

--
Regards,
Tom Ogilvy

"Michael Vaughan" wrote in message
...
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

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

Sorry Tom,

I had to give it a shot. Of course I ran into problems, but your code that
you did for me works great. Thanks for all your help. Hope that I can
return the favor someday!!!
Couldn't wait?

--
Regards,
Tom Ogilvy

"Michael Vaughan" wrote in message
...
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