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

dmacdougall

New Member
Joined
Oct 18, 2005
Messages
43
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
The mis-match is occurring because Excel is looking for the column number, not the column letter. Change your data to a number (including the declaration) and that should do the trick!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Are you sure it's the array causing the problem?

You've declared datDateCheck as a Date. Is the value in the cells actually a date?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Why not just use the numerics

aryRnwlArray(0) = 7 ' column "G"

I think you're producing this

... cells(1, G )

where you need

... cells(1, "G")
 

dmacdougall

New Member
Joined
Oct 18, 2005
Messages
43

ADVERTISEMENT

Darn it! You're right! The first row of my data sheet has headers, and I'm trying to read the date from those headers, rather from the second row on down... thanks! I always seem to miss the obvious... :) dm
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Barrie Davidson said:
The mis-match is occurring because Excel is looking for the column number, not the column letter. Change your data to a number (including the declaration) and that should do the trick!
Barrie

As far as I know you can actually use the column letter for Cells.
Code:
Cells(1, "A")  = "Barrie"
 

dmacdougall

New Member
Joined
Oct 18, 2005
Messages
43

ADVERTISEMENT

Jon -- you are right on that one -- I'm just used to refering to the columns with letters, but I can't seem to get quotes to work, so I'm just going to switch to using the numerical references... thanks! :) dm
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
datDateCheck = Worksheets("Sheet1").Cells _
(intRnwlCounter, "" & (aryRnwlArray(intArrayCounter) & ""))
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Norie said:
As far as I know you can actually use the column letter for Cells.
Code:
Cells(1, "A")  = "Barrie"

Holy Smokes!!! :eek: I never knew that. Thanks Norie - I just learned something new. :cool:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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/
 

Watch MrExcel Video

Forum statistics

Threads
1,118,820
Messages
5,574,509
Members
412,599
Latest member
Schu94
Top