VBA Converting time using TimeValue function

[Edleets]

Hi
I am trying to convert a cell that has a time exported as text into a cell that will contain the numeric value of time. I am using the function TimeValue(cell) but I am getting a type mistmatch error. Can anyone tell me what I am missing? this is how I am doing it (in a loop):

Sheet7.Cells(j, ColumnaNewDatos + 5) = TimeValue(Sheet6.Cells(k, ColumnaReloj + 5))


Thanks

 

[Yongle]

Test cell value by formula
Does Excel think the cell contains a number?
- test by formatting the cell as a number to 5 decimal places
- if you get a number then that is the source of your problem

If the cell does not contain a number, does Excel not recognise the value as "time"?
- test with a formula in adjacted cell
=TIMEVALUE(A1)
- if formula returns #VALUE! then Excel cannot interpret cell contents in the way you want

Another formula to see how Excel "sees" the value
=UPPER(CELL("type",A1)) returns L for string, V for numeric

VBA

Sub ConvertTextTime()
    MsgBox VarType(ActiveCell)                  'string is type 8
    MsgBox VarType(TimeValue(ActiveCell))       'date (incl time) is type 7
End Sub

If you cannot solve this yourself, paste cell value into your reply
- perhaps the text must be converted if it looks different to the red value below
- I tested with cell values containing text that look like this 08:36:41

 

[Edleets]

Thanks Yongle.
The cell reference (A1) where the data to copy is located is a text looking like time (12:01:10). I tested the cell to check if it is numeric, or text and it is text. When I use the formula Timevalue(A1) in the spreadsheet it is working fine, it gives me the same time in numeric value (12:01:10 PM). It is only when I use the same formula Timevalue in the VBA code that doesn't work.

Thanks

 

[MARK858]

Your code worked fine for me, what happens with the code below and what is Cells(j, ColumnaNewDatos + 5) formatted as before you run the macro?

Sheet7.Cells(j, ColumnaNewDatos + 5) = TimeValue(Trim(Sheet6.Cells(k, ColumnaReloj + 5)))

 

[Yongle]

Here is another way to try to find out what is wrong
- the message box tells you which cells caused an error

'[COLOR=#006400][I]add these 2 variables[/I][/COLOR]
    Dim myRng As Range, myStr As String

[I][COLOR=#006400]'insert these lines around your original line[/COLOR][/I]
    On Error Resume Next
[COLOR=#ff0000]    Sheet7.Cells(j, ColumnaNewDatos + 5) = TimeValue(Sheet6.Cells(k, ColumnaReloj + 5).Text)
[/COLOR]    Set myRng = Sheet6.Cells(k, ColumnaReloj + 5) 
    If Err.Number > 0 Then myStr = myStr & vbCr & myRng.Address(0, 0) & vbTab & myRng
    On Error GoTo 0

[I][COLOR=#006400]'before End Sub[/COLOR][/I]
   MsgBox myStr

 

[Edleets]

Mark858
Cells J are data comming from a report generated by a clock where people punches time in & out. I pasted all info from this report into one sheet in excel. The time data is text (I tested using "istext()"). If I transfer this data to another sheet where I need them to do operations to calculate total working time and extra hrs, it copies the data as text and no math calculations are made.
Therefore, I need to transfer the data(time) in a numeric value. This clock report sometimes has some text as "FS" instead of time for wekend and holidays. If I do manually copying the whole range of time-data and paste special selecting "multiplying" it copies as numeric. But I need to do it automatically in the VBA code. IF I also use timevalue(cellj) in the WS it works, it is only in the VBA code that gives me the error type mistmatch. Thanks

 

[MARK858]

So what happened when you ran the line of code I asked you to?

 

[Yongle]

Therefore, I need to transfer the data(time) in a numeric value. This clock report sometimes has some text as "FS" instead of time for wekend and holidays.

above would cause your code to fail

FIX
- to handle the error try something like this

    On Error Resume Next  '[COLOR=#006400][I]prevents code failing[/I][/COLOR]
    Sheet7.Cells(j, ColumnaNewDatos + 5) = TimeValue(Sheet6.Cells(k, ColumnaReloj + 5))
    If Err.Number > 0 Then
        If Sheet6.Cells(k, ColumnaReloj + 5) = "FS" Then
            Sheet7.Cells(j, ColumnaNewDatos + 5) = "[I][COLOR=#ff0000]what value should cell show?[/COLOR][/I]"
        Else
            Sheet7.Cells(j, ColumnaNewDatos + 5) = "[I][COLOR=#ff0000]what value should cell show?[/COLOR][/I]"
        End If
    End If
    On Error GoTo 0 [I][COLOR=#006400]'re-set error handling[/COLOR][/I]

 

[Edleets]

Thanks to both of you (Mark858 & Yongle). It was fixed! the conditions on errors developed by Yongle worked fine. The two IF options are either "FS" or "0" (in case of empty cells or NA cells) since those values do not count in calculations.

Mark858, the values that I got when running your code was a list of cells with the FS label and/or empty cells. You are great guys, Thanks!

 

[Yongle]

Glad you have fixed your problem
Thanks for the feedback