G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

Use the isdate function to make sure cell is date format. Error 13 is caused
by non-date values being passed to datavalue

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
if isdate(cell) then
cell.Value = TimeValue(cell)
end if
Next cell
End Sub


"build" wrote:

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

Thanks Joel,
But that gives a "type mismatch"
cheers


"Joel" wrote in message
...
Use the isdate function to make sure cell is date format. Error 13 is
caused
by non-date values being passed to datavalue

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
if isdate(cell) then
cell.Value = TimeValue(cell)
end if
Next cell
End Sub


"build" wrote:

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

I think you need activecell.currentregion. Activerange isn't correct.


Sub TEXT_TO_TIME()
For Each cell In ActiveCell.CurrentRegion
If IsDate(cell) Then
cell.Value = TimeValue(cell)
End If
Next cell
End Sub

"build" wrote:

Thanks Joel,
But that gives a "type mismatch"
cheers


"Joel" wrote in message
...
Use the isdate function to make sure cell is date format. Error 13 is
caused
by non-date values being passed to datavalue

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
if isdate(cell) then
cell.Value = TimeValue(cell)
end if
Next cell
End Sub


"build" wrote:

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

I don't think that TimeValue function can handle fractions of a second.

Because it appears that you are trying to convert text times to time values
I suggest that you enter your times on the worksheet as times and not as text.

Just as an added extra, if you need to manipulate fractions of a second on
the worksheet using VBA then you probably need to use a workaround something
like the following example:-

For Each cell In Range("A1:A9")
cell.Value = cell.Value + 0.15 / 86400
Next cell

The above adds 0.15 seconds to an existing time in the cell. (86400 seconds
in a day)

I included the added extra because your question indicated that you are
using fractions of a second and no doubt you need to manipulate them.

Regards,

OssieMac


"Joel" wrote:

I think you need activecell.currentregion. Activerange isn't correct.


Sub TEXT_TO_TIME()
For Each cell In ActiveCell.CurrentRegion
If IsDate(cell) Then
cell.Value = TimeValue(cell)
End If
Next cell
End Sub

"build" wrote:

Thanks Joel,
But that gives a "type mismatch"
cheers


"Joel" wrote in message
...
Use the isdate function to make sure cell is date format. Error 13 is
caused
by non-date values being passed to datavalue

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
if isdate(cell) then
cell.Value = TimeValue(cell)
end if
Next cell
End Sub


"build" wrote:

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

You can try this

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(left(cell,instr(cell,".") - 1))
Next cell
End Sub


"OssieMac" wrote:

I don't think that TimeValue function can handle fractions of a second.

Because it appears that you are trying to convert text times to time values
I suggest that you enter your times on the worksheet as times and not as text.

Just as an added extra, if you need to manipulate fractions of a second on
the worksheet using VBA then you probably need to use a workaround something
like the following example:-

For Each cell In Range("A1:A9")
cell.Value = cell.Value + 0.15 / 86400
Next cell

The above adds 0.15 seconds to an existing time in the cell. (86400 seconds
in a day)

I included the added extra because your question indicated that you are
using fractions of a second and no doubt you need to manipulate them.

Regards,

OssieMac


"Joel" wrote:

I think you need activecell.currentregion. Activerange isn't correct.


Sub TEXT_TO_TIME()
For Each cell In ActiveCell.CurrentRegion
If IsDate(cell) Then
cell.Value = TimeValue(cell)
End If
Next cell
End Sub

"build" wrote:

Thanks Joel,
But that gives a "type mismatch"
cheers


"Joel" wrote in message
...
Use the isdate function to make sure cell is date format. Error 13 is
caused
by non-date values being passed to datavalue

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
if isdate(cell) then
cell.Value = TimeValue(cell)
end if
Next cell
End Sub


"build" wrote:

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

That looks like it already is a time.

If you're data is really text and you want to convert it to times, you could
try:

Selecting the range
edit|Replace
what: : (colon)
with: : (same colon)
replace all
and give that range a custom format of
hh:mm:ss.000
(or whatever you want)

If you really want a macro, record one when you do it manually.


build wrote:

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

--

Dave Peterson