Problem will CELLS(Row,Col).Value when accessing dates

I'm using the following VBA statement as part of a routine to copy Sales Invoice info to another sheet in Excel 2003:

Sheets("Unpaid").Cells(UnpaidRow, 11).Value = _
Sheets(2).Cells(SalesRow, 49).Value

The value being copied to Sheet(2) is a date.

The statement works fine providing the date being copied is less or equal to the current date. If the date is in advance of the current date then I get "Error 13 - type mismatch"

I've no idea why this doesn't work as the cells in question both contain valid date values.

Any help would be welcome.

Both are declaired as Integers:

Dim SalesRow As Integer
Dim UnpaidRow As Integer

Originally Posted by romperstomper

I can't see how you would get a type mismatch error from that. Do you have any change event code in sheets(2) that might be being triggered?

No nothing; but it only generates an error when the date is greater than the current date, so I dn't see how any change would effect that?

Originally Posted by romperstomper

It would depend on what the event code did.

So you're saying that if you run the code with all cells having dates less than today, it works but if you change a date to a date after today, it fails on that cell? If so, can you post a sample of the workbook?

Thats correct, the routine runs fine if all the dates are equal or less than the current date. If I increment the date to a future date I get the error 13 with the above code highlighted in the VBA code window.

I can't really post the whole spreadsheet as it's quite large and has our companies sales info. However, the whole routine is below:


Sub FindUnpaidInvoices()

' Search for Unpaid invoices

Dim SalesRow As Integer
Dim UnpaidRow As Integer
Dim RowCounter As Integer
Dim MaxInvoices As Integer
Dim InvoiceEntered As Boolean
Dim IsUnpaid As Boolean

Application.ScreenUpdating = False

' Clear Existing Data
Sheets("Unpaid").Range("C20").Select
Range("UnpaidTable").ClearContents
Range("C20").Select

' Set start values
SalesRow = 11
UnpaidRow = 20
MaxInvoices = Range("Total_Invoices").Value

For i = 1 To MaxInvoices
InvoiceEntered = IsDate(Sheets(2).Cells(SalesRow, 3))
IsUnpaid = Not (IsDate(Sheets(2).Cells(SalesRow, 27)))

' MsgBox InvoiceEntered & "" & IsUnpaid & " " & SalesRow & " - " & Sheets(2).Cells(SalesRow, 27)

If InvoiceEntered And IsUnpaid Then

' Invoice Date
Sheets("Unpaid").Cells(UnpaidRow, 3).Value = _
Sheets(2).Cells(SalesRow, 20).Value
' Account Number
Sheets("Unpaid").Cells(UnpaidRow, 4).Value = _
Sheets(2).Cells(SalesRow, 21).Value
' Client Name
Sheets("Unpaid").Cells(UnpaidRow, 5).Value = _
Sheets(2).Cells(SalesRow, 22).Value
' Invoice Number
Sheets("Unpaid").Cells(UnpaidRow, 6).Value = _
Sheets(2).Cells(SalesRow, 23).Value
' Invoice Month
Sheets("Unpaid").Cells(UnpaidRow, 7).Value = _
Sheets(2).Cells(SalesRow, 24).Value
' VAT
Sheets("Unpaid").Cells(UnpaidRow, 8).Value = _
Sheets(2).Cells(SalesRow, 11).Value
' WHT
Sheets("Unpaid").Cells(UnpaidRow, 9).Value = _
Sheets(2).Cells(SalesRow, 12).Value
' Amount Due
Sheets("Unpaid").Cells(UnpaidRow, 10).Value = _
Sheets(2).Cells(SalesRow, 14).Value
' Days over due (Total Days less 30)
Sheets("Unpaid").Cells(UnpaidRow, 11).Value = _
Sheets(2).Cells(SalesRow, 49).Value - 30

UnpaidRow = UnpaidRow + 1

End If

SalesRow = SalesRow + 1
Next i

Application.ScreenUpdating = True

End Sub

If I change any of the dates to a future date, it generates the error 13, no matter their position. If I set the date back to the current date or less the routine completes succesfully. All the date cells in the column refernced are correctly formatted as dates and contain date values.

I've tried debugging it and it always halts at the future date, no matter where in the column of dates that is.

Thanks I think you've spotted my problem.

AW15 is the number of days from when an invoice has been issued. It's calculated by a formula and I've just noticed when an advance date is enetered it generates a #NUM error, hence the failure of my routine.

I need to correct my formulae.

Many thanks for all your help .. everybody !

It's much appreciated.