Excel // VBA // Code need to convert formula results to the results after a certain date

[Evremonde]

Hello All:

I have a spreadsheet that performs countif functions in cells D4 to D46 (data on another tab). Cell D1 contains a date. I need some VBA code that will convert the countif results (cells D4 to D46) to values and to do this on the date contained in cell D1. I need to perform this same routine in cells E4 to E46. Cell E1 contains the date. Again, I need to perform this same routine for the same cell patterns through column S. Can you assist...?

 

[Evremonde]

The formulas produce dates that are formatted as dates. Short dates i.e. 10/11/16... Formula in cell D1 is =C1. Formula in cell E2 is =D1+7...

 

[BQardi]

But does the formula produce Excel dates?
If it's the case then the formatting ie. short date format, shouldn't affect the code.

What is in C1 (the source)?

 

[Evremonde]

The source is a cell in another tab in the same worksheet. That cell is formatted is a short date as well. All cells are formatted as short dates. I assume this means an "Excel date". I start with cell C3 that contains =today(). In C2 I can (if necessary) enter a number e.g. 5. Cell C1 contains =C3+C2. This allows me to artificially produce a future date in cell C1. C1 is the source cell we speak of...

 

[BQardi]

The formulas produce dates that are formatted as dates. Short dates i.e. 10/11/16... Formula in cell D1 is =C1. Formula in cell E2 is =D1+7...

I'm a little confused with your formula in post #11: E2 is =D1+7. Should it be E1 is =D1+7?

Either way I have tried to reproduce your issue, but the code works no matter what I do by changing D4:D46 unless I put a positive value in C2, of course then it shouldn't work since all the dates are after todays date.
So if you are experiencing something else, I don't exactly know what your issue is...

Do you perhaps expect another outcome from the code?

 

[Evremonde]

BQ:

I kept working with this. In another spreadsheet it works fine. Not sure what is going on in the first but thank you so very much...

 

[BQardi]

Well, only partial help, but you're welcome...