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:-

Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electronic lock to gain entrance to a building?

The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.

The key card software produces data which can be imported into Excel 2007 in the following manner:-

Sheet 1

Cell A1 contains the date on which the key card was used. (DD/MM/YY format)

Cell B1 contains the time that the key card was used. (This column is not pertinent to this problem)

Cell C1 contains the identification number of the key card used to open the door.

The dates in column A start at the earliest and proceed down the sheet, in order, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used on one or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.

The key card numbers in column C are in random order.

The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)

The desired readout would be as follows:-

Sheet 2

Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.

The desired result when the formulae run would be:-

Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And so on all across the sheet.

Misc. Info

Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.

Sheet 1 Columns A, B & C will typically have about 10000 rows.

There may be typically up to approximately 30 key card numbers which are not used on any specific day.

Mike



Sub setup4testing()

x = 41275

For n = 1 To 10000

If (n Mod 323) = 0 Then x = x + 1

Cells(n, 1).Value = CDate(x)

Cells(n, 3).Value = Int(Rnd * 41) + ((Int(Rnd * 5) + 1) * 100)

Next

End

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

'column headings, plus filler

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 Sheet1.Cells(L0, 1).Value < curdate Then

'report absenteeism for this date

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

Next L0

Cells(2, 1).EntireRow.Delete

Set working = Nothing

End Sub

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.
Mike