Excel 2000-populating fields

[luceze]

Being quite ignorant in the area of VB coding in Excel I was wondering if someone could help me with a problem
I have a spreadsheet with formulas that go down a column. However, I need to change the formula. The problem lies in the fact that the formulas appear in an irregular pattern. For example, there is a formula in t7, t11, t12, t18, etc.
I was wondering if there is a way to update these formulas with the new formula without copying and pasting or autofilling which ruins the formatting. Also, for all the cells that have formulas in them there is data in the cells two columns to the left.
An example of the formula is =SUM(Q13:S13)/3 I need it to be =SUM((Q13:S13)/2
If anyone has any ideas I would love to see them.

Thanks,
Eric

 

[dneufarth]

I think Edit/Replace will work here.

Just select all the cells that have the same change and edit/replace all from the menu bar.


Dave

 

[luceze]

Dave,

That was my first idea. The problem that I ran into is that replacing the 3 with a 2 changes all the cell references that have number 3 in them thus screwing up a lot of the calculations.

 

[ilses]

The following replaces the last character of a formula by "2". It would work for the sample formula you gave, but not for much more (though you could replace 2 by 4, 5 etc).
It may do for a one-off.

Sub ReplaceDiv2()

Dim c As Range
Dim strFormulaDivOld As String
Dim strFormulaDiv2 As String

For Each c In ActiveSheet.UsedRange
If c.HasFormula Then
strFormulaDivOld = Left(c.Formula, Len(c.Formula) - 1)
strFormulaDiv2 = strFormulaDivOld & 2
c.Formula = strFormulaDiv2
End If
Next c

End Sub

If you have more of this, consider posting to the VBA forum.


IS

 

[luceze]

Ilses,

That worked great. Thanks for your help!

Eric