M
Michael Harrison
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 thedoor.
The dates in column A start at the earliest and proceed down the sheet, inorder, 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 soon 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
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 thedoor.
The dates in column A start at the earliest and proceed down the sheet, inorder, 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 soon 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