Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

hannable80

Technical User
Apr 5, 2006
28
GB
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 ?
 



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][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 



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

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

Part and Inventory Search

Sponsor

Back
Top