Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro to change a formula in muliple spreadsheets
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",NETWORKDAY S (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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro to change a formula in muliple spreadsheets
On May 19, 2:31*pm, wrote:
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",NETWORKDAY S (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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Muliple formula conditions | Excel Programming | |||
formula reference~muliple sheets | Excel Discussion (Misc queries) | |||
Macro to change a formula to a value | Excel Worksheet Functions | |||
Macro To Change a Formula to A Value | Excel Worksheet Functions | |||
Change Formula with Macro | Excel Programming |