Why can't you use this VBA w. array & cell value instead of copy paste?
 

Hi,

Can I run a vba code to "replicate" cell values to a larger part of a table
with a array solution? Say you have A1:A10 with a number serie like in the
end of this message and want to fill A14:A23, A27:36 etc with the values of
A1:A10. i'm not so god at arrays, but doesn't this code store all 10 values
into the a variable?

[ No, I DON'T want to use the excel handle and just drag it out or just
copy and paste the cells by selecting the compleate range of 300 x 13 cells
and just do one single paste to achieve this. I'm aware of this solution. I
want to be able to ALSO USE this kind of "array solution" in my further vba
code to have a easy way to "replicate" cells VALUES in one range to another
place in the workbook without using copy-paste in the code. ]

'Nothing happends with this code...

Sub UseArraysToCopyRangeCellsValues()
Dim rRow As Long
Dim counter As Long
Dim a As Variant

Let counter = 0
Let rRow = Selection.Row
Let a = Array(Range(Cells(rRow, 10), Cells(rRow + 9, 10)).Cells.Value)
Do While counter < 100
'Debug.Print a
rRow = rRow + 13
counter = counter + 1
ActiveSheet.Range(Cells(rRow, 10), Cells(rRow + 9, 10)).Cells.Value
= a
Loop
End Sub


A1 11
A2 22
A3 33
A4 44
A5 55
A6 66
A7 77
A8 88
A9 99
A10 10
A11
A12
A13
A14 11
A15 22
A16 33
....
and further down
.... ...

Why can't you use this VBA w. array & cell value instead of copy p
 

I don't really understand your question. You are confusing an array obkect
with an array formula.

To me, it seems that you just want to copy values down columns?

cl = 1
For rw = 14 to 100 step 13
With Range(Cells(rw,cl),cells(rw + 9,cl))
.Value = Range(Cells(1,cl),cells(10,cl))
End With
Next


when cl = 1 the code will replicate column A.
your example shows 1,14,27 as the first cell of the 'blocks' so that the
last row will be 9+ first row, and that the first row of any block is 13 rows
after the the first row of the preceding block - hence the step 13 in the
for/next loop

HTH
"Maria J-son" wrote:

Hi,

Can I run a vba code to "replicate" cell values to a larger part of a table
with a array solution? Say you have A1:A10 with a number serie like in the
end of this message and want to fill A14:A23, A27:36 etc with the values of
A1:A10. i'm not so god at arrays, but doesn't this code store all 10 values
into the a variable?

[ No, I DON'T want to use the excel handle and just drag it out or just
copy and paste the cells by selecting the compleate range of 300 x 13 cells
and just do one single paste to achieve this. I'm aware of this solution. I
want to be able to ALSO USE this kind of "array solution" in my further vba
code to have a easy way to "replicate" cells VALUES in one range to another
place in the workbook without using copy-paste in the code. ]

'Nothing happends with this code...

Sub UseArraysToCopyRangeCellsValues()
Dim rRow As Long
Dim counter As Long
Dim a As Variant

Let counter = 0
Let rRow = Selection.Row
Let a = Array(Range(Cells(rRow, 10), Cells(rRow + 9, 10)).Cells.Value)
Do While counter < 100
'Debug.Print a
rRow = rRow + 13
counter = counter + 1
ActiveSheet.Range(Cells(rRow, 10), Cells(rRow + 9, 10)).Cells.Value
= a
Loop
End Sub


A1 11
A2 22
A3 33
A4 44
A5 55
A6 66
A7 77
A8 88
A9 99
A10 10
A11
A12
A13
A14 11
A15 22
A16 33
....
and further down
.... ...