Method 'Range' of object '_Global' failed

[Brian Morris]

Hello,
I keep getting this error every so often and it does not stop until I
reboot.

Method 'Range' of object '_Global' failed

I'm writing to an Excel workbook from Access VBA.
I really don't know why it works sometimes and not other times.
Regards
Brian

Dim XL As Excel.Application
Dim XLW As Excel.Workbook
Dim XLS(5) As Excel.Worksheet
Dim rng As Excel.Range

Set XL = New Excel.Application
XL.ReferenceStyle = xlR1C1
Set XLW = XL.Workbooks.Add
XLW.Saved = False
Set XLS(1) = XLW.Worksheets.Add
.....
Set XLS(4) = XLW.Worksheets.Add
...
Set rng = XLS(1).Range(XLS(1).Cells(0 + 4, lngTotalCol + 1).Address)
rng.FormulaR1C1 = "=" & XLS(4).Name & "!RC"
rng.Select
rng.AutoFill Range(strColName(rng) & "4:" & strColName(rng) &
CStr(intNumIntervals + 4)), xlFillDefault
^^^^ gives the error

 

[George Nicholson]

Without knowing what strColName or intNumIntervals return it is impossible
to answer your question for certain, however, you might want to try adding a
explicit worksheet reference to the AutoFill Destination argument. As it
stands, VB is making some default assumptions about what sheet you intend
for the range, and relying on default assumptions is frequently be the
reason for errors like this, especially if the code works sometimes and
fails other times.

rng.AutoFill XLS(1).Range(strColName(rng) & "4:" & strColName(rng) &
CStr(intNumIntervals + 4)), xlFillDefault

Also: rng must be part of of the Destination Range you are trying to create
a reference to (per Help entry for AutoFill)

********************
If that doesn't solve the problem, in VBE: when you set a breakpoint on the
line that gives you the error, what does

? strColName(rng) & "4:" & strColName(rng) & CStr(intNumIntervals + 4)

return in the Immediate window? Is it what you expect [i.e., something that
would be an acceptable argument for Range( )] ? If not, your problem is in
those functions.

Since this only happens sometimes you might want to insert some additional
lines until you get something like this:
On Error Resume Next
rng.AutoFill XLS(1).Range(strColName(rng) & "4:" & strColName(rng) &
CStr(intNumIntervals + 4)), xlFillDefault
If Err.Number <> 0 Then
MsgBox strColName(rng) & "4:" & strColName(rng) & CStr(intNumIntervals +
4)
Exit Sub
end if
On Error GoTo 0

Hopefully the message box will give you a clue as to why an error has been
raised and which function you need to fix. Once you do that you can remove
those additional lines.


Hope this helps,