Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 SP2 VBA 6.3 'RefersTo' Method fails
I have a macro in an Excel workbook which is intended to
automatically update some information from a master template. This update may require resizing named ranges. Based upon a Sams book (I don't have the book with me at the moment so I can't give the reference) on VBA 5, I use the following code: With Range("RangeName") strNewRge = "=" & .Parent.Name & "!" strNewRge = strNewRge & Cells(.Row, .Column).Address _ & ":" strNewRge = strNewRge & Cells(.Row, ColCount).Address Names("RangeName").RefersTo = strNewRge End With When the last line is executed (the line containing "RefersTo"), I get an error message. "Run-time error '-214741848 (80010108)': Method 'RefersTo' of object 'Name' failed" Looking at the value of "strNewRge" shows it to contain the correct new range limits, properly formatted for range definition. A search in the Microsoft KnowledgeBase for this error number brings up a post-sp2 hotfix which is supposed to fix this error; however, applying this hotfix does NOT fix the error, and it still persists. Environment: Windows 2000 SP2 (5.00.2195) Excel 2002 (10.4302.4210) SP-2 Visual Basic 6.3, Version 9108, VBA: Retail 6.3.8863 Any suggestions? Bob Porter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 SP2 VBA 6.3 'RefersTo' Method fails
Hi Bob,
My guess would be a problem with the following line of code: strNewRge = "=" & .Parent.Name & "!" All manner of sheet tab names can require you to surround the name of the worksheet in single quotes. If it's required and you don't do it then your RefersTo string will be invalid. If it's not required and you include the single quotes anyway, it won't hurt anything, so I always recommend including them just to be safe. Try it like this instead: strNewRge = "='" & .Parent.Name & "'!" Note the single quote immediately after the equal sign and immediately before the exclamation point. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Robert L. Porter" wrote in message ... I have a macro in an Excel workbook which is intended to automatically update some information from a master template. This update may require resizing named ranges. Based upon a Sams book (I don't have the book with me at the moment so I can't give the reference) on VBA 5, I use the following code: With Range("RangeName") strNewRge = "=" & .Parent.Name & "!" strNewRge = strNewRge & Cells(.Row, .Column).Address _ & ":" strNewRge = strNewRge & Cells(.Row, ColCount).Address Names("RangeName").RefersTo = strNewRge End With When the last line is executed (the line containing "RefersTo"), I get an error message. "Run-time error '-214741848 (80010108)': Method 'RefersTo' of object 'Name' failed" Looking at the value of "strNewRge" shows it to contain the correct new range limits, properly formatted for range definition. A search in the Microsoft KnowledgeBase for this error number brings up a post-sp2 hotfix which is supposed to fix this error; however, applying this hotfix does NOT fix the error, and it still persists. Environment: Windows 2000 SP2 (5.00.2195) Excel 2002 (10.4302.4210) SP-2 Visual Basic 6.3, Version 9108, VBA: Retail 6.3.8863 Any suggestions? Bob Porter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 SP2 VBA 6.3 'RefersTo' Method fails
Thanks for your suggestion. Unfortunately, it did not work. I really don't think the problem is in that line of code, but rather in something that has to do with the execution of the 'RefersTo' method. This code runs on an installation in another facility. We have updated the Visual Basic in the facility where it fails to match the Visual Basic in the other facility, but the code still fails. Right now the only difference we can see in the two installations is that the one that works is running on Windows 2000 SP 4, whereas the one that fails is running on Windows 2000 SP 2. A technician from the corporate support organization is currently doing more research to see if we can find the fix that will make the difference. Bob Porter *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activate method of Worksheet class fails in Excel 2000 | Excel Discussion (Misc queries) | |||
Copy method fails in IIS | Excel Programming | |||
Open method of workbooks fails when Excel is hosted in IE | Excel Programming | |||
SaveAs method fails when saving to FTP site. | Excel Programming | |||
Goto method fails when assigning a macro to a button | Excel Programming |