Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I replace data in all sheets in a workbook at the same time? | Excel Worksheet Functions | |||
how do I do a global replace in all sheets in a workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
Find / Replace sheets vs workbook in VB | Excel Worksheet Functions | |||
Excel Macro Problem, Add-in need to work in every workbook & Error:9 Subscript out of range | Excel Programming |