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

M

mika

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
 
J

Jim Rech

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top