Help! Run time error 1004, range of object_global failed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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?
 
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.
 
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.
 
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
 
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.
 
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.
 
Back
Top