CODEWITHSUNDEEP
excelvba
mihai ratiu
mihai ratiu

Reputation: 1

VBA Time(Now) type mismatch error

I have an issue with the following code, it returns a type mismatch error message every time I open the file. The problem is that the file works fine on some PC's while, on my laptop, it triggers this error.

Private Sub Workbook_Open()

Sheets("Report").Unprotect
'
If Date <> Sheets("Report").Range("A1") Then

If Time(Now) >= 6 Then

Sheets("Report").Range("A1").Value = Date
Sheets("Report").Range("C3").Value = 0

End If
End If

Sheets("Report").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True

End Sub

Upvotes: -1

Views: 337

Answers (2)

Gary Evans
Gary Evans

Reputation: 1880

Its interesting that this works on some PCs, as Nathan_Sav shows replacing Time(now) with Hour(now()) will get rid of the error, but I'm not sure it will be the fix you require.

If Hour(now()) >= 6 Then
    Sheets("Report").Range("A1").Value = Date
    Sheets("Report").Range("C3").Value = 0
End If

The above will run the code if the time is equal to or greater than 06:00 up to 23:59.

Is this what the goal was?

Again, as Nathan_Sav has shown, other options could be: -

Second(now()) >= 6 = Run if the minute is equal to or greater than 6 seconds up to the 59th second

Minute(now()) >= 6 = Run if the hour is equal to or greater than 6 minutes up to the 59th minute

Hour(now()) >= 6 = Run if the time is equal to or greater than 06:00 up to 23:59

Day(now()) >= 6 = Run if the day is equal to or greater than 6 up to the top of the current month (i.e. 28 to 31)

Month(now()) >= 6 = Run if the month is equal to or greater than June (the 6th month) up to December

Year does not fit in in this particular list.

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

Hour(Now()) should do it. Hour Minute and Second, Day Month and Year. Type Mismatch is trying to put differing types into incorrect variable types or trying to do comparison on two types that cant, so if you press F1, you can see time returns a time, and you're comparing to an integer.

Upvotes: 1

Related Questions