Loops and Dates

Hi all, I am stumped with a problem involving a loop that searches back 1 week. As it is, my loop will search each cell looking for the date that is 7 days ago. It works fine until it finds that the date is not there. For example, 7 days ago was a Canadian holiday and therefore no update was made to my spreadsheet on the 1st of July. Being that, my loop skips right past and procedes to the top of the page and right into a runtime error. Basically, I need to implement a code that says: If the date does not exist, then stop on the day following (eg July 2). Here is my code:

Please Login or Register  to view this content.

I hope this is clear enough, please let me know if you need more.

Cheers:

Mordred

Hi please put your code in code tags.
The answer you've asked for is easy, there is also a far better way, in general, of doing this. I can share both thoughts when it won't get me in trouble for not following the forum rules...

Sorry about that, the tags are on.

First:

Please Login or Register  to view this content.

can go to:

Please Login or Register  to view this content.

(which hasn't really helped much)
when you say stop on the day following (e.g. 2nd July) - are your dates ascending or descending down the page?

Like
01 Jul
02 Jul
03 Jul
or
03 Jul
02 Jul
01 Jul
?

The obvious thing, to me, therefore, seems to be:
NOT stepping up through every cell until finding a match (your method)
pseudocode:

Please Login or Register  to view this content.

(slightly needer way round)

NOT looping a find on the range with a descending number until a match is found (a slightly better method worth mentioning for other situations)
pseudocode:

Please Login or Register  to view this content.

(the date is irrelevant once the find 'hits')

BUT simply using a built-in function, like lookup or match...
pseudocode:

Please Login or Register  to view this content.

In your example:


<-- SOrry, I went to the pub whilst half-way through writing this-->

In your example:

Please Login or Register  to view this content.

should return the row number of the next oldest date behind your expected date
...
or something liek that


It's hard to get it right without an example, probably be able to put proper conetxt if you uploaded an example.

hth

Hi Cheeky, I had a hard time trying to implement your code and I kept getting errors in the process. So, I went back to my loops and finally, I solved it. Here is what I did:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I've tested this and retested this and it works well. However, if more than one day is missing, eg: July 1 and July 2, a runtime error is thrown. For my purposes though, I only have to worry about one missing day so that doesn't really matter.

Anyways, thank you for the energies you put into trying to help me Cheeky, I do appreciate it.

That's fine for you.
If anyone finds this thread, don't employ the solution provided. If you don't understand someone's advice explain why & where (specifically) and upload an example when asked.