XL2000/2003 Compatibility Issue

[Terry Detrie]

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

 

[Peter T]

Hi Terry,

Try changing the 18 to SrcRng.Columns.Count

Is the resize still on the sheet ?

Regards,
Peter T

 

[Dave Peterson]

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.)