1004 Error with Range/Array

[Terry Detrie]

Is there anything wrong with this code?

Private Sub CopyData()
Dim vArr As Variant, RR As Range
Dim SrcRng As Range, DestRng As Range
Dim Kiln As String
Dim X As Integer, Response As Variant, Style As Variant

' Get Header data
Set SrcRng = Worksheets("DataIn").Range("A3").CurrentRegion
vArr = SrcRng.Value
Worksheets("Report Log").Select
Set DestRng = Range("Source")
Set RR = Range("Source").Offset(DestRng.Rows.Count,
0).Resize(SrcRng.Rows.Count, 19)
RR.Value = vArr
<snip>

End Sub


On one particular occasion I had 37 rows of data to copy, and only 34
were copied (plus the error generated below). I've tried this code
numerous times with similar results.

Run-time error '1004'
Application-defined or object-defined error.

 

[OssieMac]

Without testing, the first thing I question is the following line of code:-

vArr = SrcRng.Value

How can a single variant equal the value of a range which I should think has
multiple values. (One for each cell in the range)

 

[OssieMac]

sorry, my error. it becomes and array. I'll have another look at it.

 

[OssieMac]

I tested the code and it appears to work fine. I am assuming that you run
this multiple times with new data and each time the data is to be placed
below the previous data. Is this assumption correct?

If above is correct, are you resetting the range for "Source" correctly
after copying the data? I would use one of the following lines of code to do
this:-

ActiveWorkbook.Names.Add Name:="Source", RefersToR1C1:=RR

or because you only need one cell to be named "Source" because you resize
the range for the data you could use the following:-

ActiveWorkbook.Names.Add Name:="Source", _
RefersToR1C1:=RR.Cells(RR.Rows.Count, 1)


The only other thing I can think of that might cause problems but I can't
test is memory problems assigning the array to a variant. Perhaps you could
try copy and paste instead of assigning to an array first.

 

[Terry Detrie]

I had run this code 5 times on seperate occasions with different data
sets and generated the same error.

As for resetting the range for Source, it is dynamically defined using
OFFSET function. It automatically updates (correctly, I've checked)
after copying data.

I'll try the Copy/Paste method, but it still bugs me that this equally
valid approach doesn't seem to work.

Terry

 

[OssieMac]

Will be interested in your results because the code works for me. However, it
copies over top of the previous data if run multiple times. Perhaps that is
what it is supposed to do and not as per my previous question:-

"I am assuming that you run this multiple times with new data and each time
the data is to be placed below the previous data. Is this assumption correct?"

 

[Terry Detrie]

The intended purpose is to add new data onto the end of the existing range.
There should not be any data copied over existing data.

Terry

Hello OssieMac,