Excel 2007 : Out of range error

[BigGPL]

Morning,

In worksheet, Solemn c3:c26 are dates. I created an array to contain these dates, called myArray.

In worksheet Sheet3 a1 is a date. Idea is to check if Sheet3 a1 is contained in the array.

Simple enough...for most, but I get an "Subscript out of range" error. My understanding or that error is that it is usually caused by a typo, or non-existance of a worksheet.

I've double, triple, quadruple, checked spellings, etc. Can't get rid of the error!

Would someone take a look at the code and see what it is that I messed up?

Please Login or Register  to view this content.

[davesexcel]

Would you be able to explain where the error occurs?

[BigGPL]

Would you be able to explain where the error occurs?

Hey Dave,

If I checked correctly, Debug>Step Into, everytime it steps from the line:
Set myRng1 = Worksheets(Solemn).Range("c3:I26")

That is when the error box appears.

I've attached a stripped down workbook

[royUK]

The code should be in a Standard module, not the WorkBook module

This

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

myRng1 is not declared, should be declared as a Range
myLookUp is not declared

What are you trying to do exactly? I can't see why you have the array nor the purpose of the code

[BigGPL]

The code should be in a Standard module, not the WorkBook module

This

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

myRng1 is not declared, should be declared as a Range
myLookUp is not declared

What are you trying to do exactly? I can't see why you have the array nor the purpose of the code

First of all I have to tell you that I am releatively new to Excel.
The purpose of the code is to check if today's date (Sheet3 A1) is in the list of dates in the sheet Solemn C3:C26. If it is then need to get the info from the row that corresponds to the matched date in Sheet3 to display in Main D2.

As mentioned, I'm a newbie, so if there is an easier way to check the dates I would really be grateful for some code.

BTW, Thanks for the reply.

George

[royUK]

Not sure why you need VBA. It looks to me that the dates in the Table are not actual dates that Excel will recognise, but Text

[royUK]

Here's how to do this with formulas

[BigGPL]

Hey Roy,

Thanks for your help in this matter, and thanks for the worksheet formula.
The Resaon for the vba is that when the workbook opens Today() is checked against the list of Solemn days, if its not in that list then it is checked to see in what Liturgical season the date falls, and then it is checked to see if Today() is a Sunday.

When all the above checking is completed the appropriate information for Today() is then displayed.

I got everything working, more or less, but the subscript out of range error. You hit upon it when you mentioned the dates were TEXT. I forgot about that. So I changed the dates to their number equivalents , ie, April 24, 2011 is 40657. Today() is in Sheet3 A1 and A1 of worksheet Solemn = Sheet3!A1 with the cell formated as number. It works.

Again, thanks to all who replied.

My code:

Please Login or Register  to view this content.

George