Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
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) -- Regards, OssieMac "Terry Detrie" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
sorry, my error. it becomes and array. I'll have another look at it.
-- Regards, OssieMac "Terry Detrie" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
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. -- Regards, OssieMac "OssieMac" wrote: sorry, my error. it becomes and array. I'll have another look at it. -- Regards, OssieMac "Terry Detrie" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
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 On Sun, 27 Jul 2008 18:24:19 -0700, OssieMac wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
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?" -- Regards, OssieMac "Terry Detrie" wrote: 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 On Sun, 27 Jul 2008 18:24:19 -0700, OssieMac wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Error with Range/Array
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, 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?" OssieMac "Terry Detrie" wrote: 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 On Sun, 27 Jul 2008 18:24:19 -0700, OssieMac wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 when setting a Series Collection.Values as Array via VB | Excel Programming | |||
Error '1004' - the specified value is out of range. | Excel Programming | |||
Error 1004 using array in Sheet select. | Excel Discussion (Misc queries) | |||
setting range().hidden=True causes range error 1004 | Excel Programming | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming |