Thread: Broken Array?

Hi everyone,

I had a bit of code I was using:

Case 1
Set wrkbkUrl = Workbooks.Open(Filename:="T:\afolder\3Networks\1 Network.xls")
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec", "TempAnalysis", "Yearly summary")).Select
Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Sheets("Jan").Select
Range("C3").Select

and it was working fine.

But then I started altering other parts of the code (8 pages of A4 when printed of) to get rid of a couple of bugs that had, as far as I could see, nothing to do with this bit of code.

Now, however, the macro just replaces the "1st Network" with the 'strNetwork1' variable in the "Jan" worksheet leaving the others alone

Does anyone know why this would just break like this?

Daminc,

Your code is not broken, but is rather the victim of Excel's Replace method flaws. The replace
method dialog has an option "Within", which is not addressable through code. So if the user has
used the replace method and chosen Within to be worksheet rather workbook, your code will not work
as you expect. The workaround is to loop through the sheets and perform the replace on each sheet.

HTH,
Bernie
MS Excel MVP


"Daminc" <Daminc.22yvcz_1139496304.0504@excelforum-nospam.com> wrote in message
news:Daminc.22yvcz_1139496304.0504@excelforum-nospam.com...
>
> Hi everyone,
>
> I had a bit of code I was using:
>
>
> Code:
> --------------------
> Case 1
> Set wrkbkUrl = Workbooks.Open(Filename:="T:\afolder\3Networks\1 Network.xls")
> SHEETS(ARRAY(\"JAN\", \"FEB\", \"MAR\", \"APR\", \"MAY\", \"JUN\", \"JUL\", \"AUG\", \"SEPT\",
> \"OCT\", \"NOV\", \"DEC\", \"TEMPANALYSIS\", \"YEARLY SUMMARY\")).SELECT
> Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart,
> SearchOrder:=xlByColumns, MatchCase:=False
>
> Sheets("Jan").Select
> Range("C3").Select
> --------------------
>
>
> and it was working fine.
>
> But then I started altering other parts of the code (8 pages of A4 when
> printed of) to get rid of a couple of bugs that had, as far as I could
> see, nothing to do with this bit of code.
>
> Now, however, the macro just replaces the "1st Network" with the
> 'strNetwork1' variable in the "Jan" worksheet leaving the others alone
>
>
> Does anyone know why this would just break like this?
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=510568
>

Is there any intervening code between the selection of the sheets and the
running of the replace command. It sounds like you select sheet Jan so only
one sheet is selected.

--
Regards,
Tom Ogilvy

"Daminc" <Daminc.22yvcz_1139496304.0504@excelforum-nospam.com> wrote in
message news:Daminc.22yvcz_1139496304.0504@excelforum-nospam.com...
>
> Hi everyone,
>
> I had a bit of code I was using:
>
>
> Code:
> --------------------
> Case 1
> Set wrkbkUrl = Workbooks.Open(Filename:="T:\afolder\3Networks\1
Network.xls")
> SHEETS(ARRAY(\"JAN\", \"FEB\", \"MAR\", \"APR\", \"MAY\", \"JUN\",
\"JUL\", \"AUG\", \"SEPT\", \"OCT\", \"NOV\", \"DEC\", \"TEMPANALYSIS\",
\"YEARLY SUMMARY\")).SELECT
> Cells.Replace What:="1st Network", Replacement:=strNetwork1,
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
>
> Sheets("Jan").Select
> Range("C3").Select
> --------------------
>
>
> and it was working fine.
>
> But then I started altering other parts of the code (8 pages of A4 when
> printed of) to get rid of a couple of bugs that had, as far as I could
> see, nothing to do with this bit of code.
>
> Now, however, the macro just replaces the "1st Network" with the
> 'strNetwork1' variable in the "Jan" worksheet leaving the others alone
>
>
> Does anyone know why this would just break like this?
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=510568
>

Ref:

Is there any intervening code between the selection of the sheets and the
running of the replace command. It sounds like you select sheet Jan so only
one sheet is selected.

I don't think so. The code above selects the required workbook and then supposedly selects the required worksheets and then find/replaces where necessary.

...your code will not work
as you expect. The workaround is to loop through the sheets and perform the replace on each sheet.

I was afraid of that
It means turning 3 lines of code into 50 odd.
There are 3 incidences so that's an extra 140 lines of code

Oh well, what can't be changed must be endured

Cheers guys.

Set wrkbkUrl = Workbooks.Open(Filename:="T:\afolder\3Networks\1 Network.xls")

Dim oWSheet As Worksheet

For Each oWSheet In Worksheets

oWSheets.select

Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Sheets("Jan").Select
Range("C3").Select

Next oWSheet

I've just read something and was wondering if this bit of code would be ok?

Dim

This will do just those sheets:

Dim oWSheet As Worksheet
For Each oWSheet In Sheets(ARRAY(\"JAN\", \"FEB\", \"MAR\", \"APR\", \"MAY\", \"JUN\", \"JUL\",
\"AUG\", \"SEPT\",
\"OCT\", \"NOV\", \"DEC\", \"TEMPANALYSIS\", \"YEARLY SUMMARY\"))
oWSheet.Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next oWSheet

HTH,
Bernie
MS Excel MVP


"Daminc" <Daminc.22yzao_1139501409.4816@excelforum-nospam.com> wrote in message
news:Daminc.22yzao_1139501409.4816@excelforum-nospam.com...
>
> Code:
> --------------------
> Set wrkbkUrl = Workbooks.Open(Filename:="T:\afolder\3Networks\1 Network.xls")
>
> Dim oWSheet As Worksheet
>
> For Each oWSheet In Worksheets
>
> oWSheets.select
>
> Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart,
> SearchOrder:=xlByColumns, MatchCase:=False
>
> Sheets("Jan").Select
> Range("C3").Select
>
> Next oWSheet
> --------------------
>
>
> I've just read something and was wondering if this bit of code would be
> ok?
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=510568
>

Hi Bernie, what do those backslashes mean/do? I've never seen them used in this way.

I see those backslashes as probably a copy/paste error as this:

Set wrkbkUrl = Workbooks.Open(Filename:="T:\afolder\3Networks\1 Network.xls")

For Each oWSheet In Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec", "TempAnalysis", "Yearly summary"))
oWSheet.Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next oWSheet

works great