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

M

Maria J-son

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

Patrick Molloy

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
 
M

Maria J-son

Dear Patrick,

Right! I just did the following sub, working well. I feel unsure how to use
the arrays - why doesn't I need to use a array formula to make an array
operation?

And if I just write "a = Range(Cells(rRow, 10), Cells(rRow + 9, 10))" - can
I then retrieve a selected value like the 5th cell/value in the range? I had
to study arrays more, but do you have some thoughtful word to give me,
please do so...

/Thank you
***************************************************
Sub UseArraysToCopyRangeCellsValues()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim rRow As Long
Dim counter As Long
Dim a As Variant
Let counter = 0
Let rRow = Selection.Row
a = Range(Cells(rRow, 10), Cells(rRow + 9, 10))
Do While counter < 100
rRow = rRow + 13
counter = counter + 1
ActiveSheet.Range(Cells(rRow, 10), Cells(rRow + 9, 10)) = a
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
****************************************************

Patrick Molloy said:
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 said:
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
.... ...
Click to expand...
Click to expand...
 
P

Patrick Molloy

experiment!

dim source as range
dim rw a slong
fdim cl as long
rw= 8
cl = 5
set source = Range(Cells(rw, cl), Cells(rw + 9, cl))"

msgbox source.cells(1,3)

Maria J-son said:
Dear Patrick,

Right! I just did the following sub, working well. I feel unsure how to use
the arrays - why doesn't I need to use a array formula to make an array
operation?

And if I just write "a = Range(Cells(rRow, 10), Cells(rRow + 9, 10))" - can
I then retrieve a selected value like the 5th cell/value in the range? I had
to study arrays more, but do you have some thoughtful word to give me,
please do so...

/Thank you
***************************************************
Sub UseArraysToCopyRangeCellsValues()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim rRow As Long
Dim counter As Long
Dim a As Variant
Let counter = 0
Let rRow = Selection.Row
a = Range(Cells(rRow, 10), Cells(rRow + 9, 10))
Do While counter < 100
rRow = rRow + 13
counter = counter + 1
ActiveSheet.Range(Cells(rRow, 10), Cells(rRow + 9, 10)) = a
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
****************************************************

Patrick Molloy said:
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 said:
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
.... ...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Arrays and scattered cell values or another way? 16
Cell values to an array, then array transpose to another workbook 8
Pasting a cell into bookmarked table from excel vba 1
append multiple worksheets data into a single multi-dimensional array 4
Copy & Paste Conditional Formatted duplicates w/vba 0
Open four Wbooks, copy four columns from each to Master Wbook 16
Help to show formula instead of value in a VBA code 1
Subtotal by VBA 5

Top