edit multiple formulas

[Øyvind Granberg]

Hi.

I have this 1449 fomulas in a spreadsheet with just SUM(A2:A1500) but now I
want to add an IF() statement to them all, the result being
IF(SUMMER(A2:A1500)<52;1;SUMMER(A2:A1500))

Is this possible?

--

Kind regards
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[DILipandey]

Dear Granberg,

If you this formula in different ranges, then you should "Replace" command
of Excel. For this, press ctrl+H and replace SUM(A2:A1500) with
IF(SUM(A2:A1500)<52,1,SUMMER(A2:A1500)).
If you have this formula in a row or a column then you can do it in starting
cell and then copy the formula in entire row/column.

--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Øyvind Granberg]

Dear Granberg,

If you this formula in different ranges, then you should "Replace" command
of Excel. For this, press ctrl+H and replace SUM(A2:A1500) with
IF(SUM(A2:A1500)<52,1,SUMMER(A2:A1500)).
If you have this formula in a row or a column then you can do it in
starting
cell and then copy the formula in entire row/column.

--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

--

Sorry, but no can do...
There is a large number of different formulas.
Sorry I didn't point that out in the original message :-(



Kind regards
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[Dave Peterson]

Select the range with the formulas to fix first. You can include empty cells
and cells with values, but don't include any cells with formulas that shouldn't
be changed.

Then run this macro:

Option Explicit
Sub testme01()

'I have this 1449 fomulas in a spreadsheet with just SUM(A2:A1500) but now I
'want to add an IF() statement to them all, the result being
'IF(SUMMER(A2:A1500)<52;1;SUMMER(A2:A1500))

Dim myFormula As String
Dim NewFormula As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range with formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myFormula = Mid(.Formula, 2)
NewFormula = "=if(" & myFormula & "<52,1," & myFormula & ")"
.Formula = NewFormula
End With
Next myCell

End Sub

=====
You'll notice that the code uses the USA settings (comma not semicolon). That's
because VBA is USA centric. Don't "correct" those commas!

And save your file first. If this doesn't work correctly, you can close without
saving.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Øyvind Granberg]

Select the range with the formulas to fix first. You can include empty
cells
and cells with values, but don't include any cells with formulas that
shouldn't
be changed.

Then run this macro:

Option Explicit
Sub testme01()

'I have this 1449 fomulas in a spreadsheet with just SUM(A2:A1500) but
now I
'want to add an IF() statement to them all, the result being
'IF(SUMMER(A2:A1500)<52;1;SUMMER(A2:A1500))

Dim myFormula As String
Dim NewFormula As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range with formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myFormula = Mid(.Formula, 2)
NewFormula = "=if(" & myFormula & "<52,1," & myFormula & ")"
.Formula = NewFormula
End With
Next myCell

End Sub

=====
You'll notice that the code uses the USA settings (comma not semicolon).
That's
because VBA is USA centric. Don't "correct" those commas!

And save your file first. If this doesn't work correctly, you can close
without
saving.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

Thank you...

--

Kind regards
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com