Range(Cells(iRowRef, iColRef), Cells(iRowRef, iColEnd)).Replace

The above only works on the active sheet. You either have to activate each
sheet in the macro or change the above to:

.Range(.Cells(iRowRef, iColRef), .Cells(iRowRef, iColEnd)).Replace

(Untested).

--
Jim Rech
Excel MVP
"mika " wrote in message
...
| Hi
|
| I am stuck with the following problem:
| I want to loop through some selected sheets within a workbook and
| replace within an array formula, eg
| {=AVERAGE(IF(AE34:AE47<0;AE34:AE47))}, the cell reference x (eg AE47)
| with cell reference y (eg AE44).
|
| For Each sht In ActiveWorkbook.Sheets
|
| With Range(Cells(iRowRef, iColRef), Cells(iRowRef, iColEnd)).Replace
| What:=iRowRef - 1, Replacement:=iStopAvg, LookAt:=xlPart,
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
| ReplaceFormat:=False
| End With
|
| The replacement is performed in the 1st worksheet, but not in any
| subsequent one, even though it does loop through correctly.
|
| Also, the replacement function removes the array function brackets {}
| around the formula.
|
| Any suggestions?
|
| Thanks for your assistance, Mika
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|