Conditional formula change

Posted by Jim on April 11, 2001 9:15 AM

Please help with the most efficient VBA code to perform the following. In the middle of a macro, the selected cell (B40) contains the formula (A40*6.16). I want to examine the cell 12 rows above the selected cell and if it contains the formula (A28*6.24), then I want to change the selected cell's formula to use 6.24 instead of 6.16, else leave it as is. Thanks for your assistance.

Posted by cpod on April 11, 2001 9:44 AM

If ActiveCell.Offset(-12, 0).Formula = "=A28*6.24" Then ActiveCell.Formula = "=A40*6.24"

Posted by Jim on April 11, 2001 9:51 AM

I should have indicated that the cell references in my example are relative. How would the above code be changed to maintain relativity?

Posted by Dave Hawley on April 11, 2001 9:55 AM

Hi Jim

Here is one way.

Sub TryThis()
If ActiveCell.Offset(-12, 0).Formula = "=A28*6.24" Then
ActiveCell.Replace What:="6.16", Replacement:="6.24"
End If
End Sub

Dave
OzGrid Business Applications