Copying Range Of Numbers Deletes Previously Copied Numbers In Column

I posted a similar post before, but the script had a lot of irrelevant code. Ive simplified the code for readability. Also if its still not easy to understand, instead of not replying let me know & i'll post a simpler version.

Basically I need to copy a range of numbers from an array to different cell positions, but it deletes previous copied numbers in that column. ie., copy 1,2,3,4 stored in an array to Range("F37:F52").

Im guessing I need to resize the array to match the number of values inside it. Ie., 4. But I dont know how to resize it, as I'm new to arrays.

The line causing the problem is on line 100 : Range(CLabelStartPositionx & ":" & CTotalEndPosition) = Array2

Thanks.,

Sub Arrayx()


Dim Array1 As Variant
Dim Array2() As Variant

Workbooks("testx14New4Main.xlsm").Worksheets(1).ListBox1.Clear


'Detect Last Row in Column A'                                                
    With ActiveSheet
        LastRow = Workbooks("testx14New4Main.xlsm").Worksheets(1).Cells(.Rows.Count, "A").End(xlUp).Row
    End With


Dim CellLoop(1 To 3) As Integer                                                                                                                                         
                               
CellLoop(3) = 120  ' 2Min
CellLoop(2) = 60   '1Min
CellLoop(1) = 12   '10Seconds
                                                                            

'Contains Start Position of Range To Copy To ie C37, C32, C29 '
Dim LabelPosition(1 To 3) As Integer     

LabelPosition(3) = 29 '2Min
LabelPosition(2) = 32 '1Min
LabelPosition(1) = 37 '10Seconds
                                     

'Contains End Position of Range To Copy To ie Add 12 to C37 = C53, Add 4 to C32 = C36'

Dim LoopCount(1 To 3) As Integer                                                                                                                                            
                            
LoopCount(3) = 2  '2Min
LoopCount(2) = 4  '1Min
LoopCount(1) = 12 ' 10Seconds



                                    
IncrementOuterLoop = 1
IncrementInnerLoop = 1



'OuterLoop
'Cycles Through Cell Starting Points in LabelPosition(1) ie., F37, F32, F29'

For j = 1 To 3

'Copies Cells From A1 to C8660 to Array '

Array1 = Range("a1:b8660").Value
ReDim Array2(1 To UBound(Array1), 1 To UBound(Array1, 2))


'Sets Starting Position of Cell To Copy To ie LabelPosition(1) = F37, LabelPosition(2) = F32'         
LabelPositionx = LabelPosition(IncrementOuterLoop)



'A1. Create Table'
'A1.Create Labels'


'Cycles Through Cell Starting Points in LabelPosition(1) ie., F37, F32, F29'
'Creates Specific Range To Copy Numbers 1,2,3,4 in Array2 To - ie., Creates Range("F37:F52"), Range("F32:F36") 
'Using CLabelStartPositionx & CTotalEndPosition as Range(CLabelStartPositionx & ":" & CTotalEndPosition)'

LabelStartPositionx = LabelPosition(IncrementOuterLoop)
LabelEndPosition = CellLoop(IncrementOuterLoop)

TotalEndPosition = LabelStartPositionx + LabelEndPosition

CLabelStartPositionx = "f" & LabelStartPositionx
CTotalEndPosition = "f" & TotalEndPosition



NumberOfCellsToCopyFromArray=LoopCount(IncrementOuterLoop)


'Inner Loop 
'For Loop Determines Number of Values To Copy From Array, ie 1 to 4 '
For x = 1 To 4 

'Copy Numbers 1,2,3,4 in Array1 To Array2'
Array2(IncrementInnerLoop, 1) = Array1(IncrementInnerLoop, 1)

     IncrementInnerLoop = IncrementInnerLoop + 1
     
     Next x
     


     msgbox "array2 " & Array2(IncrementInnerLoop, 1) & " CLabelStartPositionx " & CLabelStartPositionx & " CTotalEndPosition " & CTotalEndPosition & " NumberOfCellsToCopyFromArray " & NumberOfCellsToCopyFromArray

    'Copy Numbers 1,2,3,4 in Array2 to Range ie., Range("F37:F52"), Range("F32:F36")
    Range(CLabelStartPositionx & ":" & CTotalEndPosition) = Array2




IncrementOuterLoop = IncrementOuterLoop + 1
IncrementInnerLoop = 1
Erase Array2
Erase Array1
Next j

End Sub

what does the error message say? this code of yours:

    'Copy Numbers 1,2,3,4 in Array2 to Range ie., Range("F37:F52"), Range("F32:F36")
    Range(CLabelStartPositionx & ":" & CTotalEndPosition) = Array2

I've seen done many times, however I it doesn't make sense that excel would let you throw an entire array's matrix of values into an actual range. it doesn't make any sense to me whatsoever. are you sure you can do that? furthermore, I have seen this done many times too:

Range(CLabelStartPositionx & ":" & CTotalEndPosition)

with a ":" in between starting range and ending range. I'm not even sure if that is valid code. but i can't tell you for sure.

there is an issue with the forum software. uploads are getting corrupted or something. the tech team is trying to fix it. I can't look at your file. upload it to google cloud, share it publicly, and post the link here.

https://drive.google.com/

if logged in:

https://drive.google.com/drive/my-drive

you're a genius max, you can do that.

I've uploaded the file to :

https://drive.google.com/file/d/1lLB...ew?usp=sharing

ok, so you posted the code that was in the MODULE, not behind the sheet. do any of these images help? do you see what I did here for you?

Ah, I uploaded the wrong sheet, one sec ill upload the correct, my bad ...

ummmmmmmmmmm

Try https://drive.google.com/file/d/1TUi...ew?usp=sharing

below images:

1) 1st ITERATION THROUGH ''J'' => result after running the script once with no breakpoints.
2) 1st ITERATION THROUGH ''J'' => vals of vars CLabelStartPositionx and CTotalEndPosition at time of insertion from array2.
3) 1st ITERATION THROUGH ''J'' => the result of the offending line of code.
4) 1st ITERATION THROUGH ''J'' => the reason behind image #3's error-oriented output.

below images:

1) 2nd ITERATION THROUGH ''J'' => vals of vars CLabelStartPositionx and CTotalEndPosition at time of insertion from array2.
2) 2nd ITERATION THROUGH ''J'' => the result of the offending line of code.
3) 2nd ITERATION THROUGH ''J'' => the reason behind image #3's error-oriented output.
4) 1st ITERATION THROUGH ''J'' => Array1's values received into the matrix.
5) 1st ITERATION THROUGH ''J'' => Array1's redimensionalization results.

Hi,
Thanks for taking the time, how do I fix it? Im guessing its because of the size of the array, how do i resize the array, so it doesnt overwrite the values?

you responded quickly. did you even look at what I did?

what's the goal again? your first post isn't detailed enough

Yes, I looked at what you did. The amount of cells to copy exceeds 4, so it inserts blank cells. I've tried setting the value to match the number of cells to copy but it still inserts blanks, which is why posted on the forum.

yes that's true Max, but have you thoroughly looked at your code line by line? a lot of it doesn't make sense, my man. you are throwing the entire 8000+ (times 2) cells into the array at the beginning and then your doing something else later on that's completely different. is that not correct?

It doesnt copy the entire range times 2, it resizes array2 to match the size of array1 ...

Array1 = Range("a1:B8660").Value
ReDim Array2(1 To UBound(Array1), 1 To UBound(Array1, 2))

I've fixed the code. Thanks for taking the time, thanks!

good deal. hopefully what I did allowed you to gain insight. and I did NOT say it copies the range times 2. I said this:

you are throwing the entire 8000+ (times 2) cells into the array at the beginning and then your doing something else later on that's completely different.

have a good one.

What did you mean by times 2 then ...