Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
Good afternoon --
The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
Did you check to see if the range name is still valid? Maybe it has been
deleted. That is assuming that "AddNewData" is a name rather than a variable. If it is a variable is needs to be declared and assigned a value. "pdberger" wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
JLG --
I did look. On that page, the range name is there, the value is '#REF', but the correct range is listed down at the bottom of that window. TIA "JLGWhiz" wrote: Did you check to see if the range name is still valid? Maybe it has been deleted. That is assuming that "AddNewData" is a name rather than a variable. If it is a variable is needs to be declared and assigned a value. "pdberger" wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
If this code is in a General module and the AddNewData isn't on the activesheet,
you'll have trouble. worksheets("sheetwithAddNewDataNameHere").select Range("AddNewData").Select Or you could use: application.goto Range("AddNewData"), scroll:=true 'or false pdberger wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
the correct range is listed down at the bottom of that window.
I don't understand this statement. Do you mean the bottom of the dialog box windor for define name? That is where you verify the named range. Also, with this: the value is '#REF' It could be that the range is trying to link to an old workbook. I don't see any reason otherwise why the range reference wouldn't work. "pdberger" wrote: JLG -- I did look. On that page, the range name is there, the value is '#REF', but the correct range is listed down at the bottom of that window. TIA "JLGWhiz" wrote: Did you check to see if the range name is still valid? Maybe it has been deleted. That is assuming that "AddNewData" is a name rather than a variable. If it is a variable is needs to be declared and assigned a value. "pdberger" wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
Found the problem, but it raised a new question. When this workbook opens,
it also opens another workbook stored on a central corporate server, from which it downloads reference info for formulas. On the references for the range names, all the page names ('Sheet1', 'Sheet2', etc.) were replaced by this reference workbook name! So the range, instead of being "'Sheet2'!$A$1:$F$20" was "'Instrument Panel Reference Data.xls'!$A$1:$F$20". The worksheets are password protected to prevent users from changing formulas like this. This problem has happened before -- EVERY sheet reference in every formula, chart, and range reference gets changed to this remote ftp workbook file. Any idea how that might happen? The only time it appears in a macro is during a workbook_open sub that opens it so the workbook can extract data to cells. "Dave Peterson" wrote: If this code is in a General module and the AddNewData isn't on the activesheet, you'll have trouble. worksheets("sheetwithAddNewDataNameHere").select Range("AddNewData").Select Or you could use: application.goto Range("AddNewData"), scroll:=true 'or false pdberger wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
Without knowing what has happened with the workbooks, seeing all of the
relevant code, etc. it would just be a guess. But the thing that is certain is that it does not just decide to do it on its own. "pdberger" wrote: Found the problem, but it raised a new question. When this workbook opens, it also opens another workbook stored on a central corporate server, from which it downloads reference info for formulas. On the references for the range names, all the page names ('Sheet1', 'Sheet2', etc.) were replaced by this reference workbook name! So the range, instead of being "'Sheet2'!$A$1:$F$20" was "'Instrument Panel Reference Data.xls'!$A$1:$F$20". The worksheets are password protected to prevent users from changing formulas like this. This problem has happened before -- EVERY sheet reference in every formula, chart, and range reference gets changed to this remote ftp workbook file. Any idea how that might happen? The only time it appears in a macro is during a workbook_open sub that opens it so the workbook can extract data to cells. "Dave Peterson" wrote: If this code is in a General module and the AddNewData isn't on the activesheet, you'll have trouble. worksheets("sheetwithAddNewDataNameHere").select Range("AddNewData").Select Or you could use: application.goto Range("AddNewData"), scroll:=true 'or false pdberger wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004
It sounds to me like you copied (or moved) a range of cells (or a worksheet)
from that "instrumenta panel" workbook into the other workbook. And excel helped out by making sure that the names pointed back to the original workbook. I'd try to fix those names: Here are some tools that'll help you: Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm And I like Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp pdberger wrote: Found the problem, but it raised a new question. When this workbook opens, it also opens another workbook stored on a central corporate server, from which it downloads reference info for formulas. On the references for the range names, all the page names ('Sheet1', 'Sheet2', etc.) were replaced by this reference workbook name! So the range, instead of being "'Sheet2'!$A$1:$F$20" was "'Instrument Panel Reference Data.xls'!$A$1:$F$20". The worksheets are password protected to prevent users from changing formulas like this. This problem has happened before -- EVERY sheet reference in every formula, chart, and range reference gets changed to this remote ftp workbook file. Any idea how that might happen? The only time it appears in a macro is during a workbook_open sub that opens it so the workbook can extract data to cells. "Dave Peterson" wrote: If this code is in a General module and the AddNewData isn't on the activesheet, you'll have trouble. worksheets("sheetwithAddNewDataNameHere").select Range("AddNewData").Select Or you could use: application.goto Range("AddNewData"), scroll:=true 'or false pdberger wrote: Good afternoon -- The code below has run fine for months and months, and suddenly errors out. The offending line appears to be the Range("AddNewData").Select line, which gives the following error: Method 'Range' of object '_Global' failed Here's the code: Sub AddNewDataPoint() ' ' AddNewDataPoint Macro ' Macro recorded 5/9/2005 by Peter D Berger ' ' 'Unprotect sheet ActiveSheet.Unprotect ("MedMan") Range("AddNewData").Select ' HERE'S THE SPOT! Selection.Copy ActiveWindow.LargeScroll Down:=-1 Range("A6").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=28 Rows("53:53").Select Selection.ClearContents Range("A53:D53").Select Selection.Copy Range("A53").Select ActiveSheet.Paste It goes on from there, but I don't think that contributes. The code was written on Excel 2003, and is routinely used now by machines with Excel07. Any help would be greatly appreciated. TIA -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
Run-time error 1004 - General ODBC Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming |