Find and replace function Excel

[Ammarbokhari]

Hi everyone,
I need an excel function which can search different sheets in a workbook and replace the value of cell containing a particular value.
I can do it with Ctrl+H, but the value that I have to replace is repeated on different places in each sheet and if I go to each sheet by using find and replace it after confirming that its the same value to be replaced, the process is slow.
If i could use a function which can search each sheet particular region (the value to be replaced is present in a certain region and not in a particular cell) for the search criteria and then replace it to new value.
I have got as far as this function (=IF(E4="Excel",REPLACE(E4,1,5,"Good"),E4)) but this gives result in a different cell and not in the same cell.
There is another option =SUBSTITUTE() but that is only for text, and also not in the same cell
can anyone help!!
Thank you

 

[JackDanIce]

You do realise when you press CTRL+H there is an option to search the whole workbook rather than just the active worksheet:

Click CTRL+H, then Options and then under the dropdown next to Within, change this to Workbook

 

[jammer12001]

[B][FONT=Arial][SIZE=2][COLOR=#000000]Sheets("Sheet1").Select
Cells.Replace What:="Data1", Replacement:="Data2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Sheets("Sheet2").Select
Cells.Replace What:="Data1", Replacement:="Data2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Sheets("Sheet3").Select
Cells.Replace What:="Data1", Replacement:="Data2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False[/COLOR][/SIZE][/FONT][/B]

just used a macro recorder real quick. messy but should get the job done

 

[Ammarbokhari]

@JackDanIce
yes I have been using this Ctrl+H for a while now, and its awesome the way it can be used to replace values within formulas. and its working across the whole workbook is also very useful for purposes similar to what I mentioned.
I am actually trying to find a function for same/similar purpose.

@jammer12001
I am totally ignorant about VBA codes.
Please can you explain what changes I need to make in order to make it work?