Sending a formula correction to Clients via VBA or Macro

I have a master workbook (Macro Enabled) that has been distributed (not shared) that 50 clients use. There is an error in one of the formulas. I want to create a macro or write some code to correct the error by replacing the formula with the error with the correct formula. I need the client to run the correction themselves and want to make this as easy as possible.

Information:

1. There are 12 worksheets in each workbook. Each of the 12 worksheets are the same and designed as dynamic tables. The error exists in one column for worksheets that have data already in the table or one row/cell for worksheets without data.
2. Each workbook/worksheet is protected with the same password.

Any ideas for the easiest way to proceed would be great....

Thanks

I would probably put together a separate workbook with some code to have the end-user browse to the master workbook. Verify that it is the workbook, then just update the formulas:

Please Login or Register  to view this content.

Note that Excel is smart enough it will automatically update the relative references in the formula, that is A5 will be =B$5+C5 and A6 will be =B$5+C6.

You would just call this from the Thisworkbook module:

Please Login or Register  to view this content.

Thanks Walleye, I tried the code and am having problems. Here is the current code I am useing in a new workbook and getting an error of (Run-time error '1004':

The column/Cell that needs to be updated starts in O3.

Please Login or Register  to view this content.

A couple of things I am not understanging:
1. I need the client to select their workbook to update (they may have named it differantly from the original. I think the code is already doing that, but I can't tell.
2. I'm not sure if the name of the worksheet to be updated should be the actual name (2011 4th Qtr) or "Sheet1".
3. I have 12 worksheets that need the updated formula, is there a way to loop through those worksheets (only) for the update?
4. The formula correction being updated needs to extend down the column to the last record (if data is resident in that row)for each sheet.


Thanks for the help!

Two things. Your range reference has a zero at the start instead of an oh, Excel is a bit picky about that:

Please Login or Register  to view this content.

When you need to embed double-quotes in a formula, you need to put in two double-quotes for each quote you want:

Please Login or Register  to view this content.

The code I posted allows the user to select the workbook, after it is open you probably want to validate that it is actually the workbook you want by checking a tab name, property or something.

To loop through the worksheets, you could use something like:

Please Login or Register  to view this content.

I use the .find function to look for the last row:

Please Login or Register  to view this content.

Put all together:

Please Login or Register  to view this content.

Thanks again Wallyeye, I will try the code today and let you know. One last question. Although I said I have 12 worksheets to loop through, the 12 are in the middle of the workbook and there are 3-4 worksheets that do not need the update. So I guess in stead of looping through all, should I list the names of the worksheets to update? And if so, how would that look in code?

Thank you

If there is a pattern, you can just check the name:

Please Login or Register  to view this content.

if not, a case statement:

Please Login or Register  to view this content.