Macro will work for 2003 and 2007 but not with 2000 !

[hannable80]

Option Explicit
Sub auto_open()

Dim i As Integer

Dim contracts(2000) As Variant
Dim contractsEmpty(2000) As Variant
Dim contractsExpired(2000) As Variant
Dim contractString As String
Dim contractStringEmpty As String
Dim contractStringExpired As String

Sheets("VBA Sheet").Activate

i = 4

Do While IsEmpty(Cells(i, 3)) = False

If IsEmpty(Cells(i, 7)) = False And ((Cells(i, 7).Value - Date) < 90) Then '++++ this is the line that thorws an error ++++
contracts(i - 3) = Cells(i, 3).Value
contractString = contractString & vbCrLf & "Contract(No. " & Cells(i, 2).Value & ")" & vbTab & contracts(i - 3)
End If

If IsEmpty(Cells(i, 7)) = False And ((Date - Cells(i, 7).Value) > 0) Then
contractsExpired(i - 3) = Cells(i, 3).Value
contractStringExpired = contractStringExpired & vbCrLf & "Contract(No. " & Cells(i, 2).Value & ")" & vbTab & contractsExpired(i - 3)
End If

If IsEmpty(Cells(i, 7)) Then
contractsEmpty(i - 3) = Cells(i, 3).Value
contractStringEmpty = contractStringEmpty & vbCrLf & "Contract(No. " & Cells(i, 2).Value & ")" & vbTab & contractsEmpty(i - 3)
End If

i = i + 1
Loop

MsgBox "Expire contract:" & vbLf & vbTab & contractString, vbInformation, "Please Note!"
MsgBox "Contracts expired:" & vbLf & vbTab & contractStringExpired, vbInformation, "Please Note!"
MsgBox "No Expire Date available:" & vbLf & vbTab & contractStringEmpty, vbInformation, "Notice"

End Sub




This will work in 2003 and 2007 any ideas ?

 

[SkipVought]

Hi,

When it errors, hit DEBUG button.

select the Date variable, right-click and select Add Watch

What is indicated in the Watch Window?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

furthermore, what is the value of i? Use the same technique.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hannable80]

the cause was the cell format