Automating weekly formula changes

Morning all,

The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.

Please Login or Register  to view this content.

etc.

When I run the report, I would do a find and replace within the entire workbook and replace 12-2-09 with 12-9-09 to get the above formula. Other people may need to run this report in the future and I would like to automate this formula change.

I was hoping that on the opening of the file the user would be presented with a simple 2 line form.

What was last Wednesday's date?
What was this Wednesday's date?

If both questions are answered, all replacements would be made without intervention. The user should also have the ability to cancel out of the form without making any formula changes.

This one was a bit tricky for me. First, I wasn't able to do it on your example, because I can only use excel 03 files (.xls). So you may have to modify the code. Right now I have it checking the first 1092 rows on the 1st column, but those numbers can be changed easily.

One thing to remember is the "Last Wednesday's Date" will have to be typed in the Input Box EXACTLY as it appears in the formula. I've tested on a sheet of my own and this code works great for me. Let me know if it works for you.

Please Login or Register  to view this content.

dave

I'm just leaving work for the day. I may not get a chance to try it until Monday. I'll repost with the results then. thanx for looking into it!!!

dave

I had a quick chance to try it here at home tonight. Unfortunately, without thinking, I didn't have access to the linked file for a new week because it is on my work network. I had to hit cancel 900 plus times for linking to the file. I should have created a mock file with the correct name....


Anyway, it seemed to work but only on the Totals! sheet. What modifications would be needed to have the code make the same changes to all three sheets in the workbook?

The changes are only needed in M2:M50 on Plant Section Sort! M2:M50 on Work Center Sort! I2:I3 and J2:J1000 on Totals! However it seemed to run fine with the wildcard range you had for the Totals! sheet.

Thanks

The procedure is set up to work on whichever sheet is active. To set it to work on different sheets, write Sheets("YourSheetName").Activate somewhere before the For... loop.

Next, to work on different columns and rows, use the For i = ... and For j = .. to set different ranges. The i controls the rows and the j controls the columns.

For example, to run on a tab named "Plant Section Sort" M2:M50 and a tab named "Work Center Sort" M2:M50, the code could look like this:

Please Login or Register  to view this content.

davegugg

Thanks for the followup. I was able to set it to work for all the sheets on exactly the ranges i needed. I appreciate the extra directions to help me tweak it. Have a great holiday season!