type mismatch:error 13

[earp_]

Hi,
when I open my excel file i've got this error

Run Time error 13
Type Mismatch

and this line

If .Value >= TimeSerial(9, 0, 0) And .Value < TimeSerial(9, 5, 0) Then

of this structure

If .Value >= TimeSerial(7, 0, 0) And .Value < TimeSerial(7, 30, 0) Then
With ThisWorkbook
Worksheets("Sheet1").Range("B2").Copy
Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues
End With
If (myEnter = 0) Then
myEnter = 1
End If
End If

is underlined.
If I run stop the macro and run it again, I have no problem.
Why?

 

[Andrew Poulsom]

How is .Value qualified, ie what is its parent object?

 

[earp_]

I see....
the parent object is

With Sheets("Sheet1").Range("J2")

but this value is #NA when I open my excel file and becomes a value after 2 seconds.
So I guess that is the reason.
Is there any way to put a "sleep" or something like that, for 3 seconds sleep?

 

[Andrew Poulsom]

To wait for 3 seconds:

Application.Wait Now + TimeValue("0:00:03")

 

[earp_]

thanks. I will try if in this way I can fix the error.

 

[earp_]

mmm strange I'm still getting the error.
and in J2 the format is

<table x:str="" style="border-collapse: collapse; width: 78pt;" border="0" cellpadding="0" cellspacing="0" width="104"><col style="width: 78pt;" width="104"><tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt; width: 78pt;" x:num="0.46809027777777779" align="right" width="104" height="20">11:14:03 AM</td> </tr></table>

while the macro starts with

With Sheets("Sheet1").Range("J2")
'7.00
If .Value >= TimeSerial(7, 0, 0) And .Value < TimeSerial(7, 30, 0) Then