Solved Change Excel 2010 code

Microsoft Excel 2003 (full)
January 25, 2012 at 13:03:19
Specs: Windows XP, 1.0Gb
I have an Excel 2010 workbook that contains 12 spreadsheets that uses names of people in various function statements. It is now necessary to change these names. Is there a way I can do a search and replace to locate them or must I go through each line of code separately?

Thank you.

Brian W


See More: Change Excel 2010 code

Report •


✔ Best Answer
January 29, 2012 at 14:01:37
re: I did not use tables and lists because at the time I wrote this code, I did not know how to use them.

Perhaps now would be a good time to "fix" the spreadsheet so you don't have to deal with this hard coding issue again.

re: how would I print out the formulas/functions...

Did you try to print the sheets while in Formula View? You might be surprised by what happens.

re: "...so I can check to make sure all of the substitutions were made."

Why not just search for the words that you replaced? If they aren't found, then all of the substitutions were made.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 25, 2012 at 13:32:04
Find and Replace?

Also look here if the text is actually embedded in formulas.


Report •

#2
January 25, 2012 at 14:58:26
Perhaps if you gave us some examples of the "various function statements" in question we could offer something more specific.

This is a perfect example of why it's a good practice to use tables and lists instead of hard coding things like names or specific values inside functions and formulas.

By referencing the table or list in the formulas, you only need to change the table or list and the change will be "global".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
January 29, 2012 at 05:44:54
ijack and DerbyDad03,

I tried "find and Replace" and it seemed to work correctly. I did not use tables and lists because at the time I wrote this code, I did not know how to use them.

Somewhat related, how would I print out the formulas/functions, so I can check to make sure all of the substitutions were made.

Thank you.
Brian W


Report •

Related Solutions

#4
January 29, 2012 at 14:01:37
✔ Best Answer
re: I did not use tables and lists because at the time I wrote this code, I did not know how to use them.

Perhaps now would be a good time to "fix" the spreadsheet so you don't have to deal with this hard coding issue again.

re: how would I print out the formulas/functions...

Did you try to print the sheets while in Formula View? You might be surprised by what happens.

re: "...so I can check to make sure all of the substitutions were made."

Why not just search for the words that you replaced? If they aren't found, then all of the substitutions were made.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question