Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
Ref: Is there any intervening code between the selection of the sheets an the running of the replace command. It sounds like you select sheet Jan s only one sheet is selected. I don't think so. The code above selects the required workbook and the supposedly selects the required worksheets and then find/replaces wher necessary. ...your code will not work as you expect. The workaround is to loop through the sheets and perfor 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 -- Damin ----------------------------------------------------------------------- Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707 View this thread: http://www.excelforum.com/showthread.php?threadid=51056 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
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 oWShee ------------------- I've just read something and was wondering if this bit of code would b ok -- Damin ----------------------------------------------------------------------- Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707 View this thread: http://www.excelforum.com/showthread.php?threadid=51056 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
Hi Bernie, what do those backslashes mean/do? I've never seen them used in this way. -- Daminc ------------------------------------------------------------------------ Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074 View this thread: http://www.excelforum.com/showthread...hreadid=510568 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Broken Array?
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 :) -- Daminc ------------------------------------------------------------------------ Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074 View this thread: http://www.excelforum.com/showthread...hreadid=510568 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Broken Links | Setting up and Configuration of Excel | |||
Excel Broken? | Excel Worksheet Functions | |||
Broken Links.... | Excel Discussion (Misc queries) | |||
i think its broken | Excel Discussion (Misc queries) | |||
Tab Stop Broken | Excel Programming |