Modify formulas in range based on cell value

I have two sheets with formulas like the following.

=IF(Slct_Report=1,HWF!B12,AWF!B21)
=IF(Slct_Report=1,HWF!B9,AWF!B10)

I'm trying to write a macro to remove the 'if' statement so that I get:

=HWF!B12

or

=AWF!B10

based on the value of Slct_Report

Thanks very much for your help...

I'm removing the 'IF' statement because the sheets are being copied to a new workbook. The new workbook will contain either the sheet named HWF or AWF plus the two sheets (Summary1 and Summary2) I mentioned that contain the IF statements. So the process so far is to copy the three sheets to the new workbook, then modify the IF statement to remove the reference to the sheet that does not exist. I'm open to a different design suggestion.

Originally Posted by 6StringJazzer

Install this code in a Module, and call it for each of the two worksheets you want it to work on.

This assumes that the named range will contain and be compared to a numerical value in all formulas. I only tested for your examples but made it general enough so that it should work for any named range, and any expressions for the TRUE and FALSE parts.

Jeff this looks interesting but I couldnt get the code to execute. Each time I hit run or step into I was prompted to select a macro to run.

Thanks!!

This macro requires a single argument, which is the worksheet containing the formulas you want to change. You cannot run a Sub with arguments by running it by itself, it has to be called from somewhere else to provide the arguments.

If this is a one-shot deal, I suggest you try this:

1. With your file open in Excel, hit ALT-F11 to open the VBA development window
2. Hit CTRL-G to open the Immediate window
3. In the first line of the Immediate window type:

Please Login or Register  to view this content.

where Sheet1 is the actual name of the worksheet containing the formulas you want to modify. Then hit Enter and the macro will run for that worksheet.

Originally Posted by 6StringJazzer

This macro requires a single argument, which is the worksheet containing the formulas you want to change. You cannot run a Sub with arguments by running it by itself, it has to be called from somewhere else to provide the arguments.

If this is a one-shot deal, I suggest you try this:

1. With your file open in Excel, hit ALT-F11 to open the VBA development window
2. Hit CTRL-G to open the Immediate window
3. In the first line of the Immediate window type:

Please Login or Register  to view this content.

where Sheet1 is the actual name of the worksheet containing the formulas you want to modify. Then hit Enter and the macro will run for that worksheet.

When I try this I get an invalid procedure call or argument


on line:

RangeName = Mid(Arr(0), 5, InStr(2, Arr(0), "=") - 5)

Here is the actual file I'm working on. Thanks very much!

I took your file, unchanged, and typed the following commands into the Immediate window. There were no errors and it worked fine.

Please Login or Register  to view this content.

If you use this Sub on worksheets that contain IF formulas other than the type that you described above then that might cause the error you saw. This Sub is very specific to the type of formula you mentioned and I did not write it to be robust enough to ignore other IF formulas.

Originally Posted by 6StringJazzer

I took your file, unchanged, and typed the following commands into the Immediate window. There were no errors and it worked fine.

Yes I got it working! Do you think there is a way to get it to perform on the newly created workbook? If you can take another look, when I call ResolveFormulas from the Sub CopytoNewWB() macro, (the call is currently commented out) I get the compile error. I think its because Slct_Rport doesnt reside in the new workbook.

Originally Posted by podunkus

...I get the compile error. I think its because Slct_Rport doesnt reside in the new workbook.

First, let me emphasize that the code looks for a formula of this format:

=IF(<range expression>=<numeric constant>,<expression>,<expression>)

If the formula doesn't conform to this syntax, the code will give unpredictable results, including runtime errors.

If there is no range called Slct_Rport what do your formulas look like? If you use a named range in formulas that does not exist, it will cause a formula error on the worksheet. It will also cause the code to fail--it will not give you a compile error, it will give you a runtime error. Whenever asking for help on an error, please give the entire error message, and the line of code that causes it.

ResolveFormulas requires a worksheet as an argument; the error message is telling you that the argument is missing. One way to specify the worksheet is

Please Login or Register  to view this content.

You must be using it correctly when calling from CopytoNewWB; you just need to do the same thing here.

Sorry for the long delay, it's been a very busy month. What were the values of C.Formula and FalseExp prior to reaching this statement?