Replacing "Yes" with End of Month Date

[JMathur]

Hey All:

I'm building a monthly Excel report that will run on Macros, and I need some help with some formulas. In every report there will be cells that contain the words "Yes." I need a way to replace the "Yes" with the last day of the month. An Example:

I run the report on the 14th of December, and I receive 12 "Yes" fields as a result. I want a formula to take all the "Yes" fields, and replace them with the last day of the month (in this example, 12/31/13). Since this report will be run everyday for the forseeable future, the 'End of Month date' field would have to update automatically.

Since these reports will be run on a daily basis, it would be too cumbersome to manually use a 'replace with' filter. I'm trying to find a way to completely automate it; this way I can macro the entire process. This is the final outcome that I'd like:

Depending on which month I am running the report in, Excel automatically finds and displays the last date of that month, and then takes that date, and replaces all the "Yes" fields with the last date of the month.

Please Help!

[Tony Valko]

Not possible to do with formulas unless you use a macro to insert the formula into the cells in question.

However, if you're already using macros for other things then use a macro to replace the cells in question with the last day of the month. Why do you want a formula?

[6StringJazzer]

This code placed in the object module of the worksheet containing the data will find every cell whose content is the string "Yes" and replace it with the date of the last day of the month in which when the macro is run.

Please Login or Register  to view this content.

[Evan_lin]

I think it's this.

[Evan_lin]

This's OK.

[bruce71101]

There is a function called EOMONTH(start_date,months)
you can simply input the following formula in the cell

Please Login or Register  to view this content.