formula or macro to change a formula in muliple spreadsheets

[mel.pilgrim]

I have a workbook with 4 tabs. I want to be abl to change the formula
that is located in
the rows on the 2nd tab and 3rd tab from 'networkdays' to
'nb.jours.ouvres'.

What I would need to do is search the spreadsheet (workbook) for any
references to 'networkdays' and replace it with 'nb.jours.ouvres'.

The main reference area is on my tab called 'Retro'. Cell I4 to I43
contain the reference to 'Networkdays'.

Here is the formula that I would want to change the networkdays to
nb.jours.ouvres.
English version: IF(AUTORATE!B8=0,0,IF(AUTORATE!$E$5="D",NETWORKDAYS
(B4,C4)*G4/H4,NETWORKDAYS(B4,C4)*(H4/5)*G4/H4))

French version: IF(AUTORATE!B8=0,0,IF(AUTORATE!$E
$5="D",NB.JOURS.OUVRES(B4,C4)*G4/H4,NB.JOURS.OUVRES(B4,C4)*(H4/5)*G4/
H4))

If this can be accomplisted, then it would be a matter of locating a
button on the input section of my workbook and then it would work on
both English versions of Excel and French versions of Exel.

Many thanks
Mel

 

[meh2030]

I have a workbook with 4 tabs.  I want to be abl to change the formula
that is located in
the rows on the 2nd tab and 3rd tab from 'networkdays' to
'nb.jours.ouvres'.

What I would need to do is search the spreadsheet (workbook) for any
references to 'networkdays' and replace it with 'nb.jours.ouvres'.

The main reference area is on my tab called 'Retro'.  Cell I4 to I43
contain the reference to 'Networkdays'.

Here is the formula that I would want to change the networkdays to
nb.jours.ouvres.
English version: IF(AUTORATE!B8=0,0,IF(AUTORATE!$E$5="D",NETWORKDAYS
(B4,C4)*G4/H4,NETWORKDAYS(B4,C4)*(H4/5)*G4/H4))

French version:  IF(AUTORATE!B8=0,0,IF(AUTORATE!$E
$5="D",NB.JOURS.OUVRES(B4,C4)*G4/H4,NB.JOURS.OUVRES(B4,C4)*(H4/5)*G4/
H4))

If this can be accomplisted, then it would be a matter of locating a
button on the input section of my workbook and then it would work on
both English versions of Excel and French versions of Exel.

Many thanks
Mel

Mel,

Have you tried using the REPLACE function in Excel (i.e. Ctrl + h)?
The code below is very basic (recorded from the macro recorder for
Ctrl + h and then modified) and does not perform any data checks
because I don't know what your situation warrants. However, this
should get you started on something.

Best,

Matthew Herbert

Sub ReplaceFormulas()
Dim strFind As String
Dim strReplace As String
Dim Wks As Worksheet

strFind = InputBox("Find What", "Find What")
strReplace = InputBox("Replace With", "Replace With")

For Each Wks In ActiveWorkbook.Worksheets
With Wks
.Cells.Replace What:=strFind, Replacement:=strReplace, LookAt
_
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With
Next
End Sub