I have two named ranges in each sheet of my export template (All named ranges have a scope of the worksheet):
- "Data" consists of contiguous rows where I write data and formulas
- "Formulas" are just the columns with formulas
I have it this way because CopyFromRecordset only works contiguously, so contained in the recordset are text representations of the formulas, and then after the data is written, the formulas of all of the written cells in the formula range are set to the values of these cells. Here is a picture for clarity:
This works perfectly the first time, but every time after that I get a 1004 error: either "Application Defined or Object Defined error" or "Method 'Intersect' of object '_Global' failed" when calling the Intersect method.
I can't escape this situation without the task manager, and this error happens every time I try it until I restart my computer.
Code:
Dim XLwbk As Excel.Workbook
Dim XLsht As Excel.Worksheet
Dim XLrange As Range
Dim rs As DAO.Recordset
On Error GoTo FuncError
...
Set rs = CurrentDb.OpenRecordset(..., dbOpenSnapshot)
rs.MoveFirst
Set XLsht = XLwbk.Worksheets(1)
With XLsht
.Activate
Set XLrange = .range("Data")
XLrange.CopyFromRecordset rs
Set XLrange = .range(XLrange.Row & ":" & XLrange.Row + rs.RecordCount - 1) 'Section of "Data" range that is USED
Dim v As Range
For Each v In Intersect(XLrange, .range("Formulas")).Areas 'THIS LINE ERRORS
v.FormulaR1C1 = v.Value
Next v
End With
GoTo FuncSuccess
FuncError:
MsgBox Err.number & " " & Err.Description
FuncSuccess:
Set XLsht = Nothing: Set XLrange = Nothing: Set rs = Nothing
Any suggestions?
Thanks!