Using An Array as a Counter -- Why "Type Mismatch"

[dmacdougall]

OK, I want to check through multiple columns and rows for dates which may have passed, so I can be notified when I open the workbook. Why does the following code give me a Type Mismatch error when I attempt to fill the "datDateCheck" variable? Help! dm


Private Sub Workbook_Open()

'This sub displays a MsgBox if any items are past due

Dim intRnwlCounter As Integer
Dim aryRnwlArray(7) As String <-- Perhaps this should be Variant?
Dim datDateCheck As Date
Dim intArrayCounter As Integer

intRnwlCounter = 1
intArrayCounter = 0

aryRnwlArray(0) = "G"
aryRnwlArray(1) = "K"
aryRnwlArray(2) = "O"
aryRnwlArray(3) = "S"
aryRnwlArray(4) = "W"
aryRnwlArray(5) = "AA"
aryRnwlArray(6) = "AE"

For intArrayCounter = 0 To 6

For intRnwlCounter = 1 To 100

datDateCheck = Worksheets("Appointments").Cells _
(intRnwlCounter, (aryRnwlArray(intArrayCounter))) <-- Type
Mismatch
error occurs
here!

If datDateCheck < Now() And datDateCheck > 0 Then
MsgBox ("You have expired appointments which should be renewed!"), _
Buttons:=48
Exit Sub 'Exits when first expired appointment found
Else
End If

Next

Next

End Sub

 

[just_jon]

dmacdougall

There is no need to use the number.

I've just lightly tested the code and the only way I was able to get an error was to put text in one of the cells being checked/

And, as usual, Norie's right...

 

[dmacdougall]

Jon -- thank you again, but I don't quite understand how you are using the string concatentation operator (&) with the double quotes:

datDateCheck = Worksheets("Sheet1").Cells _
(intRnwlCounter, "" & (aryRnwlArray(intArrayCounter) & ""))


Wouldn't the above resolve to be this:

datDateCheck = Worksheets("Sheet1").Cells _
(intRnwlCounter, ""..."")

With the elipise being the column letter? Or does the above resolve to:

(intRnwlCounter, "...")

And if so, how does that work? Please let me know if you have time -- I'm don't quite understand why the two quotes on each side are needed... thanks! dm

 

[Barrie Davidson]

And, as usual, Norie's right...

Yeah, had my piece of humble pie as well. :wink:

Perhaps a beer would make the pie go down easier.

 

[just_jon]

Jon -- thank you again, but I don't quite understand how you are using the string concatentation operator (&) with the double quotes:

datDateCheck = Worksheets("Sheet1").Cells _
(intRnwlCounter, "" & (aryRnwlArray(intArrayCounter) & ""))


Wouldn't the above resolve to be this:

datDateCheck = Worksheets("Sheet1").Cells _
(intRnwlCounter, ""..."")

With the elipise being the column letter? Or does the above resolve to:

(intRnwlCounter, "...")

And if so, how does that work? Please let me know if you have time -- I'm don't quite understand why the two quotes on each side are needed... thanks! dm

No, you don't need them -- I was sadly in error about that.

Just ignore my posting entirely! I'll try to!