If you can explain this...

[DaK]

Okay, here is the situation. I have code that looks at dates. Depending on the current date, a certain procedure is run.

The vba code looks at a named range called "date" that contains a week in it (ex. range "date" might have something like "5/13 - 5/19" that represents a weeks time)

Now, the range "date" gets its info by beeing linked to another cell. The cell it is linked to is a vlookup that looks at the current date and compares it to a list of dates to determine what week the current date belongs too. (ex. today is 5/13 so it would return that 5/13 belongs to the week "5/13 - 5/19, thus the range "date" would display "5/13 - 5/19"). Have I lost anyone yet???

Now, the cell with the vlookup knows what date to display by looking at another cell that counts "True" statements(get to this in a minute). For expample, if the cell counting true statements value was 20, the vlookup would look at 20 (which is the 20th week of this year) and know to display 5/13 - 5/19.

Now for the True statements, this is where things are getting messed up. The formlua read like this:
=IF($J$70>L70,TRUE,FALSE)
This formula works great except that if the current date falls on the beginning of the week if displays a false instead of true. (ex. today is Monday 5/13, the begining of the week is 5/13 so they are equal and not greater than, this cause the cell that counts true statements to be one short.)

So, what have I done so far.....
I have change the formula to have >= instead of just >. I have reversed everything so that it counts falses instead of true to display the correct date. I have tried adding +1 to the end of the formula so that is adds the extra to the cell that counts true statements.

The problem is, when I change the formula to any of the above ways that I mentioned, the vba code no longer works for some reason. Let me post the vba code for you to look at:

If Range("date") = "1/1 - 1/6" Then
do1
ElseIf Range("date") = "1/7 - 1/13" Then
do2
ElseIf Range("date") = "1/14 - 1/20" Then
do3
ElseIf Range("date") = "1/21 - 1/27" Then
do4
ElseIf Range("date") = "1/28 - 2/3" Then
do5
ElseIf Range("date") = "2/4 - 2/10" Then
do6
ElseIf Range("date") = "2/11 - 2/17" Then
do7
ElseIf Range("date") = "2/18 - 2/24" Then
do8
ElseIf Range("date") = "2/25 - 3/3" Then
do9
ElseIf Range("date") = "3/4 - 3/10" Then
do10
ElseIf Range("date") = "3/11 - 3/17" Then
do11
ElseIf Range("date") = "3/18 - 3/24" Then
do12
ElseIf Range("date") = "3/25 - 3/31" Then
do13
ElseIf Range("date") = "4/1 - 4/7" Then
do14
ElseIf Range("date") = "4/8 - 4/14" Then
do15
ElseIf Range("date") = "4/15 - 4/21" Then
do16
ElseIf Range("date") = "4/22 - 4/28" Then
do17
ElseIf Range("date") = "4/29 - 5/5" Then
do18
ElseIf Range("date") = "5/6 - 5/12" Then
do19
ElseIf Range("date") = "5/13 - 15/19" Then
do20
ElseIf Range("date") = "5/20 - 5/26" Then
do21
ElseIf Range("date") = "5/27 - 6/2" Then
do22
ElseIf Range("date") = "6/3 - 6/9" Then
do23
ElseIf Range("date") = "6/10 - 6/16" Then
do24
ElseIf Range("date") = "6/17 - 6/23" Then
do25
ElseIf Range("date") = "6/24 - 6/30" Then
do26
ElseIf Range("date") = "7/1 - 7/7" Then
do27
ElseIf Range("date") = "7/8 - 7/14" Then
do28
ElseIf Range("date") = "7/15 - 7/21" Then
do29
ElseIf Range("date") = "7/22 - 7/28" Then
do30
ElseIf Range("date") = "7/29 - 8/4" Then
do31
ElseIf Range("date") = "8/5 - 8/11" Then
do32
ElseIf Range("date") = "8/12 - 8/18" Then
do33
ElseIf Range("date") = "8/19 - 8/25" Then
do34
ElseIf Range("date") = "8/26 - 9/1" Then
do35
ElseIf Range("date") = "9/2 - 9/8" Then
do36
ElseIf Range("date") = "9/9 - 9/15" Then
do37
ElseIf Range("date") = "9/16 - 9/22" Then
do38
ElseIf Range("date") = "9/23 - 9/29" Then
do39
ElseIf Range("date") = "9/30 - 10/6" Then
do40
ElseIf Range("date") = "10/7 - 10/13" Then
do41
ElseIf Range("date") = "10/14 - 10/20" Then
do42
ElseIf Range("date") = "10/21 - 10/27" Then
do43
ElseIf Range("date") = "10/28 - 11/3" Then
do44
ElseIf Range("date") = "11/4 - 11/10" Then
do45
ElseIf Range("date") = "11/11 - 11/17" Then
do46
ElseIf Range("date") = "11/18 - 11/24" Then
do47
ElseIf Range("date") = "11/25 - 12/1" Then
do48
ElseIf Range("date") = "12/2 - 12/8" Then
do49
ElseIf Range("date") = "12/9 - 12/15" Then
do50
ElseIf Range("date") = "12/16 - 12/22" Then
do51
ElseIf Range("date") = "12/23 - 12/29" Then
do52
ElseIf Range("date") = "12/30 - 12/31" Then
do53
End If

End Sub

And when I jump into the code (f8) I hover the mouse over Range("date") which will say that is equals 5/13 - 5/19 but when it gets to the if statement for it, it just keeps on going like the 2 don't match each other. Yaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah.... Can someone explain why it works one way and not the others. They all make the date appear correctly in the "date" range that the vba code needs, why oh why does it not work.

Any explainaition would be greatly appreciated. Also, I know that I could just change the week dates to start with sunday but that would be too easy.

TIA DaK

[DaK]

Actually, it seems any changes I make to the cells results in the code not working.
If I change the formulas, I change the dates, I insert or delete rows.... Any slight change I make to any cells make the code not work???? What is going on???
I was going to do the sunday thing I mentioned before but when I change the 5/13 to a 5/12, the code didnt' work!!!

AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH

Thank you for your patience.

DaK

[Asala42]

G'day,

If you want to send me your file I'll take a stab.

asala42@yahoo.com

Adam