VBA/Excel macro - trying to simplify code using an array that draws from one worksheet and populates across multiple worksheets

Question

What I initially did works, but it is bulky and I am pretty sure can be streamlined. This is a snippet of the code (I repeat this process over and over about 200+ times, I had to break it into two macros):

Set rng = ws.Range("C11:AJ11")
l = 13
For Each cell In rng
Sheets(l).Range("D23") = cell.Value
Sheets(l).Range("I23") = cell.Value 
 l = l + 1
Next cell

Set rng = ws.Range("C12:AJ12")
l = 13
For Each cell In rng
Sheets(l).Range("D24") = cell.Value
Sheets(l).Range("I24") = cell.Value
l = l + 1
Next cell

So that works, but now I am trying to figure out how to use an array to potentially streamline (if that's possible? or maybe an array isn't the best way? I am very open to advice on better approaches.):

Dim row As Range
Dim rng As Range, cell As Range
Dim TestArray() As Variant
TestArray = Range("C10:AJ54")
l = 13
For Each cell In TestArray
  Sheets(l).Range("D22:D66") = cell.Value
  Sheets(l).Range("I22:I66") = cell.Value
  l = l + 1
Next cell

So I get the "Compile error: For Each control variable on arrays must be Variant" which I did look into, but I am not fully sure how to correct it. I am also worried that I won't populate the sheets in the correct order that I want. It should pull from one row at a time, and one cell in order to each sheet first. Then it should go to the next row of the activesheet and take values from each cell of THAT row and put one value in each sheet in order, etc. etc.

I hope that is a good amount of detail for what I am asking, but if not, I am happy to try to explain more :)

Scott Craner · Accepted Answer · 2016-06-21 00:38:54Z

1

Do another outer loop that loops through the rows:

Sub foo()
Dim i As Long
Dim rng As Range
Dim l As Long
Dim cell As Range

For i = 10 To 54
    Set rng = ws.Range("C" & i & ":AJ" & i)
    l = 13
    For Each cell In rng
        Sheets(l).Range("D" & 12 + i) = cell.Value
        Sheets(l).Range("I" & 12 + i) = cell.Value
         l = l + 1
    Next cell
Next i
End Sub

If you want to use an array then you will still need to loop twice:

Sub foo()
Dim i As Long
Dim rng As Range
Dim l As Long
Dim j As Long
Dim testArr() As Variant
Dim outarr() As Variant

testArr = ActiveSheet.Range("C10:AJ54")
ReDim outarr(LBound(testArr, 1) To UBound(testArr, 1)) As Variant
For j = LBound(testArr, 2) To UBound(testArr, 2)
    l = 13
    For i = LBound(testArr, 1) To UBound(testArr, 1)
        outarr(i) = testArr(i, j)
    Next j
    Sheets(l).Range("D22").Resize(UBound(outarr)).Value = outarr
    Sheets(l).Range("I22").Resize(UBound(outarr)).Value = outarr
    l = l + 1
Next i
End Sub

The array will be faster in that it only interacts with the sheets as few times as possible.

edited Jun 21, 2016 at 0:38

answered Jun 20, 2016 at 23:45

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Snackbreak Over a year ago

Yes, the first option makes a lot of sense to me - conceptually that's what I really wanted to do, but I couldn't figure out a mechanism, so I thought I would try an array. I think arrays are just too advanced for me right now. Thank you!

Collectives™ on Stack Overflow

VBA/Excel macro - trying to simplify code using an array that draws from one worksheet and populates across multiple worksheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related