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

 

[sandaflo]

very true; I built it quickly thinking the scale would no need to change, so rather then a proper lookup table, wrote the formula once then dragged it down the necessary cells

 

[ron2k]

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.

Very true! He can use the FIND/REPLACE to do that as well. Find the value he wants to replace and replace it with a cell reference.

 

[paddydive]

Hey Dave,

That was really good, i didnt knew we can use Names like that.

Just want to know more, how we enter the arrey formula in name, do we have to enter curly brackets manually in the formula. Or there is any another easiest way to define an arrey in a name.

please let me know.

 

[dave3009]

You can enter the array directly in the formula if you like but for this situation where the array is being used several times it is easier to enter in a defined name. You can of course make a table and refer to that in the defined name manager or directly in the formula

Excel Workbook
	H	I	J	K	L
11	SAT	100		SAT	100
12	SAT	100		N/A	100
13	SAT	100		UNSAT	50
14	SAT	100		REC	70
15				SELECT	0
Sheet1

Excel 2007

Cell Formulas
Range	Formula
I11	=VLOOKUP(H11, my_table, 2, 0)
I12	=VLOOKUP(H12, K11:L15, 2, 0)
I13	=VLOOKUP(H13, {"SAT",100;"N/A",100;"UNSAT",50;"REC",70;"SELECT",0}, 2, 0)
I14	=VLOOKUP(H14, my_table2, 2, 0)

Excel Workbook
Name	Refers To
my_table	={"SAT",100;"N/A",100;"REC",70;"UNSAT",50;"SELECT",0}
my_table2	=Sheet1!$K$11:$L$15
Workbook Defined Names

 

[paddydive]

thanks dave3009,

That was really helpful.