Excel VBA - Range.Replace within workbook over several sheets problem
 

Hi

I am stuck with the following problem:
I want to loop through some selected sheets within a workbook an
replace within an array formula, e
{=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)).Replac
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 an
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, Mik

--
Message posted from http://www.ExcelForum.com

Excel VBA - Range.Replace within workbook over several sheets problem
 

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/
|