Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
When it bombs out, enter Debug and see what 'j' evaluates to and check to see
what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
I don't see anything wrong with your code. Run it again and choose debug
when it bombs, then check the value of recset(j).name by typing this into the immediate window: ?recset(j).Name That should at least make sure that you are trying to write an actual value to the range. HTH Die_Another_Day "in-over-his-head-bill" wrote in message ... I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
Thanks for the help; still don't see the answer.
in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
ww = "$A$1"
j = 0 ? Range(ww).Offset(0, j).Address $A$1 -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: Thanks for the help; still don't see the answer. in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
I figured out a fix late last night without understanding why I needed the
fix. By hardcoding some range values instead of assigning the recordset to the range, I discovered that even though WorkBook 2 was the active workbook; it was trying to place the newly created range on Workbook 1 (I am still not sure why it errored out as opposed to just putting the range on WB 1). I replaced all my Range... statements with Worksheet(wz).Range ... where wz=Range(the user specified range name in Workbook 2).Worksheet.Name and it works. Thanks to all for all their help. "Tom Ogilvy" wrote: ww = "$A$1" j = 0 ? Range(ww).Offset(0, j).Address $A$1 -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: Thanks for the help; still don't see the answer. in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
Obviously you have your code in the Thisworkbook module or a sheet module.
However, code like this: Public Sub manageoutput_exists() shouldn't be in one of those modules, but in a general module. It certainly occured to me that the problem could be caused by that, but when I checked your declaration, it wasn't an event (the only thing which should be in these modules), so I discounted that as the problem. (too hasty on my part) The cause is that an unqualified range reference located in a sheet module refers to that sheet. You have stumbled into the solution of qualifying it. In a general module, and unqualified range reference refers to the active sheet. -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: I figured out a fix late last night without understanding why I needed the fix. By hardcoding some range values instead of assigning the recordset to the range, I discovered that even though WorkBook 2 was the active workbook; it was trying to place the newly created range on Workbook 1 (I am still not sure why it errored out as opposed to just putting the range on WB 1). I replaced all my Range... statements with Worksheet(wz).Range ... where wz=Range(the user specified range name in Workbook 2).Worksheet.Name and it works. Thanks to all for all their help. "Tom Ogilvy" wrote: ww = "$A$1" j = 0 ? Range(ww).Offset(0, j).Address $A$1 -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: Thanks for the help; still don't see the answer. in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
Thank you for your help. I didn't entirely follow your response (hence my
name in-over-his-head...) But, the public sub manageoutput_exists() is in a general module (module1). Public variables (declared in the general module) savetofile and savetorange are given values based on a user double-click on a list box value contained in a userform. Also in userform_activate I open the 2nd workbook as follows: Workbooks.Open(savetofile).Activate. I have a command button on the initial worksheet the user sees when opening up this app. The following code is the only code I have in a sheet module: Private Sub cmd1_Click() Call setglobalvars 'sets connection, opens connection Set formproxy = UserForms.Add("frm_rptmenu") formproxy.Show End Sub Does any of this cause the unqualified range reference even though I have most of the code in the general module? Bill "Tom Ogilvy" wrote: Obviously you have your code in the Thisworkbook module or a sheet module. However, code like this: Public Sub manageoutput_exists() shouldn't be in one of those modules, but in a general module. It certainly occured to me that the problem could be caused by that, but when I checked your declaration, it wasn't an event (the only thing which should be in these modules), so I discounted that as the problem. (too hasty on my part) The cause is that an unqualified range reference located in a sheet module refers to that sheet. You have stumbled into the solution of qualifying it. In a general module, and unqualified range reference refers to the active sheet. -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: I figured out a fix late last night without understanding why I needed the fix. By hardcoding some range values instead of assigning the recordset to the range, I discovered that even though WorkBook 2 was the active workbook; it was trying to place the newly created range on Workbook 1 (I am still not sure why it errored out as opposed to just putting the range on WB 1). I replaced all my Range... statements with Worksheet(wz).Range ... where wz=Range(the user specified range name in Workbook 2).Worksheet.Name and it works. Thanks to all for all their help. "Tom Ogilvy" wrote: ww = "$A$1" j = 0 ? Range(ww).Offset(0, j).Address $A$1 -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: Thanks for the help; still don't see the answer. in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
No, the unqualified range in a general module would refer to the active
sheet regardless of which workbook is running the code. This is one the the ways one would generalize their code. -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote in message ... Thank you for your help. I didn't entirely follow your response (hence my name in-over-his-head...) But, the public sub manageoutput_exists() is in a general module (module1). Public variables (declared in the general module) savetofile and savetorange are given values based on a user double-click on a list box value contained in a userform. Also in userform_activate I open the 2nd workbook as follows: Workbooks.Open(savetofile).Activate. I have a command button on the initial worksheet the user sees when opening up this app. The following code is the only code I have in a sheet module: Private Sub cmd1_Click() Call setglobalvars 'sets connection, opens connection Set formproxy = UserForms.Add("frm_rptmenu") formproxy.Show End Sub Does any of this cause the unqualified range reference even though I have most of the code in the general module? Bill "Tom Ogilvy" wrote: Obviously you have your code in the Thisworkbook module or a sheet module. However, code like this: Public Sub manageoutput_exists() shouldn't be in one of those modules, but in a general module. It certainly occured to me that the problem could be caused by that, but when I checked your declaration, it wasn't an event (the only thing which should be in these modules), so I discounted that as the problem. (too hasty on my part) The cause is that an unqualified range reference located in a sheet module refers to that sheet. You have stumbled into the solution of qualifying it. In a general module, and unqualified range reference refers to the active sheet. -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: I figured out a fix late last night without understanding why I needed the fix. By hardcoding some range values instead of assigning the recordset to the range, I discovered that even though WorkBook 2 was the active workbook; it was trying to place the newly created range on Workbook 1 (I am still not sure why it errored out as opposed to just putting the range on WB 1). I replaced all my Range... statements with Worksheet(wz).Range ... where wz=Range(the user specified range name in Workbook 2).Worksheet.Name and it works. Thanks to all for all their help. "Tom Ogilvy" wrote: ww = "$A$1" j = 0 ? Range(ww).Offset(0, j).Address $A$1 -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: Thanks for the help; still don't see the answer. in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Run time error 1004, range of object_global failed
Thanks again for the help. At least the fix worked however I stumbled across
it. What I am writing now is "demo" code -- I will go back later if and when I need to expand/improve on the app and see if I can figure out why the unqualified range problem popped up. "Tom Ogilvy" wrote: No, the unqualified range in a general module would refer to the active sheet regardless of which workbook is running the code. This is one the the ways one would generalize their code. -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote in message ... Thank you for your help. I didn't entirely follow your response (hence my name in-over-his-head...) But, the public sub manageoutput_exists() is in a general module (module1). Public variables (declared in the general module) savetofile and savetorange are given values based on a user double-click on a list box value contained in a userform. Also in userform_activate I open the 2nd workbook as follows: Workbooks.Open(savetofile).Activate. I have a command button on the initial worksheet the user sees when opening up this app. The following code is the only code I have in a sheet module: Private Sub cmd1_Click() Call setglobalvars 'sets connection, opens connection Set formproxy = UserForms.Add("frm_rptmenu") formproxy.Show End Sub Does any of this cause the unqualified range reference even though I have most of the code in the general module? Bill "Tom Ogilvy" wrote: Obviously you have your code in the Thisworkbook module or a sheet module. However, code like this: Public Sub manageoutput_exists() shouldn't be in one of those modules, but in a general module. It certainly occured to me that the problem could be caused by that, but when I checked your declaration, it wasn't an event (the only thing which should be in these modules), so I discounted that as the problem. (too hasty on my part) The cause is that an unqualified range reference located in a sheet module refers to that sheet. You have stumbled into the solution of qualifying it. In a general module, and unqualified range reference refers to the active sheet. -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: I figured out a fix late last night without understanding why I needed the fix. By hardcoding some range values instead of assigning the recordset to the range, I discovered that even though WorkBook 2 was the active workbook; it was trying to place the newly created range on Workbook 1 (I am still not sure why it errored out as opposed to just putting the range on WB 1). I replaced all my Range... statements with Worksheet(wz).Range ... where wz=Range(the user specified range name in Workbook 2).Worksheet.Name and it works. Thanks to all for all their help. "Tom Ogilvy" wrote: ww = "$A$1" j = 0 ? Range(ww).Offset(0, j).Address $A$1 -- Regards, Tom Ogilvy "in-over-his-head-bill" wrote: Thanks for the help; still don't see the answer. in the debugger j is 0 recset.fields.count=4 ww = $a$1 and recset(j).Name='effmonth' when I position float the cursor over the whole line In the debugger I get range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed Is range($a$1).Offset(0,0) an ok statement? "JLatham" wrote: When it bombs out, enter Debug and see what 'j' evaluates to and check to see what each side of the equation evaluates to - either Offset(0,j) is illegal, or something is really odd with the recset.Fields.Count value. Since the second option should, in theory, never be invalid or illegal, I suspect that you're offsetting into an invalid column. Look at ww at that point, look at value of j and make sure that the resultant column is legitimate. "in-over-his-head-bill" wrote: I am getting the subject line error, but only ocassionally when I run my code -- and I can't figure out a pattern to when it bombs and when it doesn't. My code is in workbook 1, I open a user-specified workbook 2, which is the active workbook when it reaches this sub. I am trying to replace the data in the user-specified range in workbook 2 with new data from a recordset and save to the same range name. savetorange is a public string variable containing the user-specified range name. Can anyone see what is wrong with the code below? Thanks. Public Sub manageoutput_exists() Dim rngResultSet As Range Dim ww As Variant ' get upper-left most cell of the current range in workbook 2 ww = Range(savetorange).Cells(1, 1).Address 'delete the range Range(savetorange).Delete 'add field headers from the results of the sql query For j = 0 To recset.Fields.Count - 1 -next line bombs Range(ww).Offset(0, j).Value = recset(j).Name Next j 'copy the recordset data below the headers ww = Range(ww).Offset(1, 0).Address Range(ww).CopyFromRecordset recset Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion rngResultSet.Name = savetorange ActiveWorkbook.Save End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
Run-time error 1004 Method "Range of object_global failed | Excel Programming | |||
Run-time error '1004': Method 'Range' of object '_Global' failed | Excel Programming | |||
Run-time error '1004': Method 'Range' of object '_Global' failed | Excel Programming | |||
Select method of range class failed, Run time error 1004 | Excel Programming |