changing incorrect formula

[sedgely]

i have "inherited" a spreadsheet, and have noticed that one of the sheets as a large range where the formulae are incorrect, to overcome this i need to append "/2" to the end of the formula in each cell. as there are hundreds of cells affected is there a way to go thru each cell/formula and append "/2" using VBA.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[rmikesmith]

Craig,

Select the region containing the formulas you wish to change then run this procedure:

Sub ModifyFormulas()
Dim OneCell As Range

   For Each OneCell In Selection
     If OneCell.HasFormula Then
       OneCell.Formula = OneCell.Formula & "/2"
     End If
   Next OneCell
   
End Sub

Note: This code will append the character combination /2 directly to the end of each formula, without regard for whether parentheses are needed to give correct result. If that is the case, you may need other manipulations of the formulas.
Make a backup copy of the data before you run the procedure, in case it doesn't do what you want.


Regards,
Mike

 

[GlennUK]

Type a 2 into any cell, and do Edit Copy, then select those formulae and do Edit/Paste Special/Divide, and it will do exactly what you want.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[sedgely]

Thanks both
Mike your code looks like it does the job, but Glenn that is so easy, will remember that one.


Cheers, Craig
Si fractum non sit, noli id reficere

 

[rmikesmith]

Bravo to Glenn!

Sadly, the following is often true for me:
VBA Solution = Trees
Excel Features = Forest


Regards,
Mike