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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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!
 
Upvote 0
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?
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
datDateCheck = Worksheets("Sheet1").Cells _
(intRnwlCounter, "" & (aryRnwlArray(intArrayCounter) & ""))
 
Upvote 0
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:
 
Upvote 0
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/
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top