Application-defined or object defined error

[EmilH]

Pease take a look at this simple code:

--------------------

Sub Workbook_Open()
CheckRows
End Sub

Sub CheckRows()
Dim temp As Long
temp = 1

Do Until Sheet1.Cells(temp, 2).Value > DateTime.Date 'this
line returns me the error in the end.

If Sheet1.Cells(temp, 2).Value = DateTime.Date Then
Sheet1.Cells(temp, 2).Interior.Color = RGB(255, 0, 0)
End If

temp = temp + 1

Loop
End Sub

--------------------

It does what I want but in the end returns 'Application-defined or
object-defined error'

What could cause this?

Thanks.
EmilH

 

[vbapro]

Try to use VBA.DateTime.Date or just Date instead of DateTime.Date

 

[Bob Phillips]

Sub Workbook_Open()
CheckRows
End Sub

Sub CheckRows()
Dim temp As Long
temp = 1

Do Until Sheet1.Cells(temp, 2).Value > Date
If Sheet1.Cells(temp, 2).Value = Date Then
Sheet1.Cells(temp, 2).Interior.Color = RGB(255, 0, 0)
End If

temp = temp + 1

Loop
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tom Ogilvy]

I don't have any problem using DateTime.Date, so I would guess that you never
have a date greater than the current date and end up looping off the sheet.

Sub Workbook_Open()
CheckRows
End Sub

Sub CheckRows()
Dim temp As Long
temp = 1

Do Until Sheet1.Cells(temp, 2).Value > DateTime.Date

If Sheet1.Cells(temp, 2).Value = DateTime.Date Then
Sheet1.Cells(temp, 2).Interior.Color = RGB(255, 0, 0)
End If

temp = temp + 1
if temp > rows.count then exit do
Loop
End Sub

Although I would certainly try to break out much earlier than that.


Sub CheckRows()
Dim temp As Long
Dim lastrow as Long
temp = 1
lastrow = Sheet1.Cells(rows.count,2).End(xlup).row
Do Until Sheet1.Cells(temp, 2).Value > _
DateTime.Date or temp > lastrow
If Sheet1.Cells(temp, 2).Value = DateTime.Date Then
Sheet1.Cells(temp, 2).Interior.Color = RGB(255, 0, 0)
End If

temp = temp + 1
Loop
End Sub