range.offset generates a Run Time error 1004
 

Hi,

In the code below I'm getting a 'Run Time error 1004: application
defined or object-defined error when I try offset a range by a value
greater than 32231. Can anybody explain why this happening. I'm
trying to append some data to the end of a dynamic range, can I use
something else instead of range.offset?

Sub AddNewLogs(MyLogArr())

Dim rngRefLogs As Range
Dim rngRefLogsDateCol As Range
Dim rngTemp As Range
Dim LastRow As Long
Dim LastCol As Integer



'activate the logs worksheet
Sheets("Logs").Activate
'set the dynmaic range
Set rngRefLogs = Range("RefLogs")
'get the lastrow in Column A
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Set temporary range offset from the end of our dynamic range by the
number of rows it contains
Set rngTemp = rngRefLogs.Offset(LastRow - 1, 0)
'extend the temporary range by the number records in our array
Set rngTemp = rngTemp.Resize(UBound(MyLogArr, 1) - LBound(MyLogArr, 1)
+ 1, 6)
'set the temporary range to the value of the array
rngTemp.Value = MyLogArr
'refresh the pivot table
ActiveWorkbook.RefreshAll
'save the work book
ActiveWorkbook.Save
'switch focus to the analysis sheet
Sheets("Analysis").Activate

End Sub



Thanks

range.offset generates a Run Time error 1004
 

Unless you are using Excel 2007, there are only 65536 rows in a worksheet.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
Hi,
In the code below I'm getting a 'Run Time error 1004: application
defined or object-defined error when I try offset a range by a value
greater than 32231. Can anybody explain why this happening. I'm
trying to append some data to the end of a dynamic range, can I use
something else instead of range.offset?

Sub AddNewLogs(MyLogArr())
Dim rngRefLogs As Range
Dim rngRefLogsDateCol As Range
Dim rngTemp As Range
Dim LastRow As Long
Dim LastCol As Integer

'activate the logs worksheet
Sheets("Logs").Activate
'set the dynmaic range
Set rngRefLogs = Range("RefLogs")
'get the lastrow in Column A
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Set temporary range offset from the end of our dynamic range by the
number of rows it contains
Set rngTemp = rngRefLogs.Offset(LastRow - 1, 0)
'extend the temporary range by the number records in our array
Set rngTemp = rngTemp.Resize(UBound(MyLogArr, 1) - LBound(MyLogArr, 1)
+ 1, 6)
'set the temporary range to the value of the array
rngTemp.Value = MyLogArr
'refresh the pivot table
ActiveWorkbook.RefreshAll
'save the work book
ActiveWorkbook.Save
'switch focus to the analysis sheet
Sheets("Analysis").Activate
End Sub
Thanks