Formula for Subtracting Dates

[kielcurtis1431]

Hi Everyone,

I have a spreadsheet which contains tenant names inside an apartment. What I would like to do is extract data from my rent roll onto a page showing who's lease is expiring in the next 90 days.

Please Login or Register  to view this content.

At the end, "val1" has dates within the cells. So it reads- If 'val1'-90 days <= Now() then ws2.range(lrow).value=ws.cells(r, 3).value

Everything works on my sheet except the formula. Any Help?

[Andrew-R]

The only problem with your formula is that it's a formula.

This bit of code:

Please Login or Register  to view this content.

:will evaluation whether the R1C1 formula in the active cell is equal to "=now()" and return -1 if it is, or 0 if it is not. It will then check if (val1-90) is less than that value. Which it's unlikely to be (unless you're dealing with a lot of dates in the early part of the 20th Century).

What you're looking for is:

Please Login or Register  to view this content.

[kielcurtis1431]

That did help quite a bit, thanks! But the "-90" part is still giving it lots of trouble. If I exclude "-90" then the code works great, but I need to know whose lease is coming due. Any suggestions?

[Andrew-R]

I'm a bit stumped by this one, I created a simplified version of your code for testing:

Please Login or Register  to view this content.

The first problem it hits is that when there's a date in A1 Excel doesn't recognise it as being a numeric, so that line needs to be changed to:

Please Login or Register  to view this content.

The code then works fine so long as there's a genuine date in cell A1. In fact the only error I can generate is if A1 contains a string which looks like a date. This seems to slip through the IsDate test, but then throws a type mismatch error when I try to subtract 90 from it.

Is that what you're seeing?

[kielcurtis1431]

I figured out the problem. But now the list the macro creates leaves out the last three people that fit my criteria. The only way they make it to the list is if I add extra fake names to the rent roll which I don't want to do. I hope that makes sense. Do you have a clue why it would do that?

[Andrew-R]

Because of the way you're counting the rows:

Please Login or Register  to view this content.

List you had 5 rows of data then this would run a CountA on the range C5:C10, which would return a value of 5, so this loop:

Please Login or Register  to view this content.

Would look from 5 to 5, not 5 to 10 as you require.

Try changing your size determining section to:

Please Login or Register  to view this content.