VBA to change formula in worksheets

[sandaflo]

Hello,

relative newbie to VBA, but I would imagine the following can be done. I have a workbook with a couple hundred worksheets, all identical in layout (templates for audit functions). witin the tabs are formulas that assign a value to a data validation dropdown value, provding an audit score.
Formula reads as

=IF(H23="SAT",100,IF(H23="N/A",100,IF(H23="UNSAT",50,IF(H23="REC",70,IF(H23="SELECT",0)))))

I would like to change the "SAT" and "N/A" values from 100 to a different value (i.e. 95), thus changing the total score.

Is there a way to loop through the workbook and find/replace the formula or change a portion of the formula? Can VBA treat a formula like a test string?

thank you

 

[ron2k]

Using FIND/REPLACE (Ctrl + H), you can look for ",100," and change it to ",95," (don't use quotes ""). Ensure that in the "Within" option you select Workbook.

Ron

 

[paddydive]

Hi Ron2K

I think above Find/replace wont work,

What i understand is that Sandalfo wants to make changes in the formula and not in the data.. if i guess it correct the above find replace will make changes in data and not in formula..

 

[ron2k]

Hi Ron2K

I think above Find/replace wont work,

What i understand is that Sandalfo wants to make changes in the formula and not in the data.. if i guess it correct the above find replace will make changes in data and not in formula..

If there is not data that actually has ",100," (without quotes), then only the formulas will be affected. To be on the safe side he can do the same in two steps:

1. Find: "SAT",100; Replace:"SAT",95

2. Find: "N/A",100; Replace:"N/A",95

 

[paddydive]

You are right, above is the simplest way... thanks Ron2K

 

[dave3009]

Or you could create a table of the values and switch to VLOOKUP this way you only need to amend the table if the values for each parameter change

 

[paddydive]

like how ?

 

[sandaflo]

Thanks, I had no idea that fnd/replace worked on formulas. One of those excel functions I need to booknmark for the future.


thanks for the quck reply; this board is full of great people and info.

 

[dave3009]

Create a table either directly in a sheet or in the defined name manager (Insert>>Name>>Define), I created a table in the name manager and called it my_table and it refers to ={"SAT",100;"N/A",100;"REC",70;"UNSAT",50;"SELECT",0}.

Then I used VLOOKUP

=VLOOKUP(H23, my_table, 2, 0)

Which will yield the same result as the OP formula of..

=IF(H23="SAT",100,IF(H23="N/A",100,IF(H23="UNSAT",50,IF(H23="REC",70,IF(H23="SELECT",0)))))

just in a more compact and efficient formula

 

[Seti]

How about changing the formulas to point to a cell that you change from 100 to 95 or whatever value you want. The up front cost will be larger in terms of time, but you won't have to do the find/replace again.