Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Run time error 1004, range of object_global failed | Excel Programming | |||
Button programming Run-time error '1004' with range | Excel Programming | |||
Run-time error '1004' on Range.Activate | Excel Programming | |||
Run-time error '1004' PasteSpecial Method of Range Class Failed | Excel Programming | |||
Run-time 1004 error on range select | Excel Programming |