Help! Run time error 1004, range of object_global failed

[in-over-his-head-bill]

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

 

[JLatham]

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.

 

[Die_Another_Day]

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]

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?

 

[Tom Ogilvy]

ww = "$A$1"
j = 0
? Range(ww).Offset(0, j).Address
$A$1

 

[in-over-his-head-bill]

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]

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.

 

[in-over-his-head-bill]

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]

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.

 

[in-over-his-head-bill]

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.