Michael Harrison wrote:

On Wednesday, February 20, 2013 9:25:02 AM UTC-7, Michael Harrison wrote:
Auric, Are you there?

A little while ago you wrote for me a routine which checked security
key card data for absentees. The routine is quite useable but has some
minor issues which I have been unable to fully understand, let alone
resolve. I wonder if you would be kind enough to take a look and see if
you can discover the problem. I enclose all of the relevant information
at the end of this message.

Part of the problem is that when I run the two routines, setup4testing
and absentee check, everything works perfectly except that absentee
check does not use the last date in column A sheet one of setup4
testing.

When I run the routine, absentee check, on the actual data received
from the key card software, the second row on sheet two, below the key
card numbers, has a zero in each cell, the routine does include the
last date in column A from sheet one, the results on sheet 2 are
accurate but the routine hangs up on the line:-

“cards(Sheet1.Cells(L0, 3).Value)=True”

Calling a Run-time error “9” subscript out of range

The only apparent difference between the data generated by
setup4testing and the actual key card software is that several cards on
the key card software are used every day and therefore do not have any
absentees to report on sheet two.

Here is the reference information:-
[snip]
Auric,
I followed your instructions as carefully as I could. The first time I
got the result "Run-time error 1004 application-defined or object
defined error", the second and third time I tried I got the number
12466. It's possible I did something different the first time but I
don't think so.

Okay, one thing that will give you error 9 at the indicated line is if
there's a blank cell in column C (the card numbers). My code assumes that
your data is formatted *perfectly*, so any deviation will result in errors.

Perhaps it's just a case of checking for blanks (although I'm not too clear
about that error 1004). Try this version of absenteeCheck:

Sub absenteeCheck()
Dim cards(100 To 540) As Boolean
Dim working As Worksheet
curdate = Sheet1.Cells(1, 1).Value
Set working = Sheets.Add
working.Activate
For L1 = 1 To 5
For L2 = 0 To 40
Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2
Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0
Next L2
Next L1
For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
If (Len(Sheet1.Cells(L0, 1).Value) 0) And _
(Len(Sheet1.Cells(L0, 3).Value) 0) Then
If Sheet1.Cells(L0, 1).Value < curdate Then
For L1 = 1 To 5
For L2 = 0 To 40
If Not cards((L1 * 100) + L2) Then
Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row _
+ 1, ((L1 - 1) * 41) + L2 + 1).Value = curdate
End If
Next L2
Next L1
Erase cards()
curdate = Sheet1.Cells(L0, 1).Value
Else
cards(Sheet1.Cells(L0, 3).Value) = True
End If
End If
Next L0
Cells(2, 1).EntireRow.Delete
Set working = Nothing
End Sub

Run it against the data generated by setup4testing first, and if it's still
okay -- and it should be -- try it against your data. If you still get
errors, I'll need to see your workbook to figure it out.

--
So this is why people become super villains.