Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2000/2003 Compatibility Issue
I wrote code in Excel 2000, and it does not work in Excel 2003 (tested this on two different computers). All I'm doing is copying range values into an array and pasting the values at the end of another range. When I try to paste the values, Excel 2003 raises a 1004 Run Time error. I've used this method for a very long time and never got errors unless I messed up matching the array/range sizes. I set a watch on the pertinent variables and got the following: SrcRng.Address = A3:R29 RR.Address = B108:S134 UBound(vArr,1) = 27 UBound(vArr,2) = 18 The contents of vArr look good, and both source and destination ranges are 27 rows, 18 columns. Set SrcRng = Worksheets("DataIn").Range("A3").CurrentRegion vArr = SrcRng.Value Set DestRng = Range("Reports") Set RR = DestRng.Offset(DestRng.Rows.Count, 0).Resize(SrcRng.Rows.Count, 18) RR.Value = vArr ' this is where 1004 error is raised Most everything I have found in forums has dealt with backward compatibility, which is an issue that we programmers hate but deal with. What I am dealing with is forward compatibility, which I didn't expect at all, especially with such a simple process like copying and pasting data. Can anyone help? Terry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2000/2003 Compatibility Issue
Hi Terry,
Try changing the 18 to SrcRng.Columns.Count Is the resize still on the sheet ? Regards, Peter T "Terry Detrie" wrote in message ... I wrote code in Excel 2000, and it does not work in Excel 2003 (tested this on two different computers). All I'm doing is copying range values into an array and pasting the values at the end of another range. When I try to paste the values, Excel 2003 raises a 1004 Run Time error. I've used this method for a very long time and never got errors unless I messed up matching the array/range sizes. I set a watch on the pertinent variables and got the following: SrcRng.Address = A3:R29 RR.Address = B108:S134 UBound(vArr,1) = 27 UBound(vArr,2) = 18 The contents of vArr look good, and both source and destination ranges are 27 rows, 18 columns. Set SrcRng = Worksheets("DataIn").Range("A3").CurrentRegion vArr = SrcRng.Value Set DestRng = Range("Reports") Set RR = DestRng.Offset(DestRng.Rows.Count, 0).Resize(SrcRng.Rows.Count, 18) RR.Value = vArr ' this is where 1004 error is raised Most everything I have found in forums has dealt with backward compatibility, which is an issue that we programmers hate but deal with. What I am dealing with is forward compatibility, which I didn't expect at all, especially with such a simple process like copying and pasting data. Can anyone help? Terry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2000/2003 Compatibility Issue
I use xl2003 and I used this:
Option Explicit Sub testme01() Dim SrcRng As Range Dim RR As Range Dim vArr As Variant Dim DestRng As Range 'some test data With Worksheets("datain") Set SrcRng = .Range("a3:r29") With SrcRng .Formula = "=rand()" .Value = .Value End With End With vArr = SrcRng.Value Set DestRng = Range("Reports") Set RR = DestRng.Offset(DestRng.Rows.Count, 0).Resize(SrcRng.Rows.Count, 18) RR.Value = vArr ' this is where 1004 error is raised End Sub And it worked ok. Any chance you have locked cells on a protected worksheet? Or something else sticking its ugly head in? (I'd test it on a brand new workbook and see if it failed there.) Terry Detrie wrote: I wrote code in Excel 2000, and it does not work in Excel 2003 (tested this on two different computers). All I'm doing is copying range values into an array and pasting the values at the end of another range. When I try to paste the values, Excel 2003 raises a 1004 Run Time error. I've used this method for a very long time and never got errors unless I messed up matching the array/range sizes. I set a watch on the pertinent variables and got the following: SrcRng.Address = A3:R29 RR.Address = B108:S134 UBound(vArr,1) = 27 UBound(vArr,2) = 18 The contents of vArr look good, and both source and destination ranges are 27 rows, 18 columns. Set SrcRng = Worksheets("DataIn").Range("A3").CurrentRegion vArr = SrcRng.Value Set DestRng = Range("Reports") Set RR = DestRng.Offset(DestRng.Rows.Count, 0).Resize(SrcRng.Rows.Count, 18) RR.Value = vArr ' this is where 1004 error is raised Most everything I have found in forums has dealt with backward compatibility, which is an issue that we programmers hate but deal with. What I am dealing with is forward compatibility, which I didn't expect at all, especially with such a simple process like copying and pasting data. Can anyone help? Terry -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 compatibility issue | Excel Discussion (Misc queries) | |||
2003/2007 Compatibility Issue - Hidden Sheets | Excel Discussion (Misc queries) | |||
Compatibility Issue between files created in 2003 but opened in 20 | Excel Discussion (Misc queries) | |||
Compatibility issue? Excel97/2003 | Excel Programming | |||
Excel 2002/2003 Macro Compatibility issue | Excel Programming |