# Multi for Array loop

#### aka 42

##### New Member
Hi guys,

How can I get the following result with the code that follows?
1
2
3
3
3
3
Result For each column...

Code:
``````Option Explicit

Const Rng1 = "A"
Const Rng2 = "B"
Const Rng3 = "C"

Const Str1 = "1"
Const Str2 = "2"
Const Str3 = "3"

Sub LoopArrTest()

Dim i As Integer
Dim Rng, Str As Variant

For i = 1 To 3
For Each Rng In Array(Rng1, Rng2, Rng3)
For Each Str In Array(Str1, Str2, Str3)

Range(Rng & i) = Str

Next Str
Next Rng
Next i

End Sub``````

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Greetings AKA,

In plain English, what would you like the loop(s) to do?

Mark

Hi Mark,

My Rng Constant expressions is from column A to BZ
My Str Constant expressions is in actual fact Pi tags

My code exceeds the max length of 64k so I'm trying to
shorten my code.

What I would like to achieve is for the integer to run down
each column then restart for the next array in the loop.

I hope this explains what I'm trying to do.

...What I would like to achieve is for the integer to run down
each column then restart for the next array in the loop.

Apologies as I am about faded. Could you explain that bit? I think I understand that we want the value to increment by 1, for each row in a column, but what does "restart for the next array in the loop" mean exactly?

Mark

Sorry for that...

For each row in first array Rng1 = Str1
Then Restart for I = 1 to 3 for next Array Rng2 = Str2

To get the result as follows:

A B C
1 2 3
1 2 3
1 2 3

Ahh, got it! Try:

In a Standard Module:

Rich (BB code):
``````Option Explicit

Sub example()
'// We happen to know the ending size of our array, so dimension it at the start.//
Dim arrLongValues(1 To 3, 1 To 3) As Long
Dim x                             As Long
Dim y                             As Long

'// y then x probabaly sounds backwards, I just usually use x as left and y as from //
'// the top to keep it clear in my poor muddled brain.                              //
'// Working with just the array first (and plunking the finished array onto the     //
'// sheet in one swoop) is much faster than updating cells one-at-a-time.           //
For y = 1 To 3
For x = 1 To 3
arrLongValues(y, x) = 1 + (x - 1)
Next
Next

'// plunk the output wherever...  //
Sheet1.Range("A1").Resize(3, 3).Value = arrLongValues

End Sub``````

Hope that helps,

Mark

y then x probabaly sounds backwards, I just usually use x as left and y as from the top to keep it clear in my poor muddled brain.
I agree, and I used to do it that way until I confused myself too many times with "which is which?". Now I declare my variables as creative misspellings of row and col (usually either rrow/ccol or rowy/colx) to keep myself straight.

Didn't know that trick with dropping an array variable into a range of cells as one big drop, that will speed up some of my massive macro workbooks, thanks for that!

@aka 42:

Sorry, replace the one line indicated. I don't know what I was thinking...

Rich (BB code):
``````Sub example()
Dim arrLongValues(1 To 3, 1 To 3) As Long
Dim x                             As Long
Dim y                             As Long

For y = 1 To 3
For x = 1 To 3
'      arrLongValues(y, x) = 1 + (x - 1)
arrLongValues(y, x) = x
Next
Next

Sheet2.Range("A1").Resize(3, 3).Value = arrLongValues

End Sub``````

@gardnertoo:
I agree, and I used to do it that way until I confused myself too many times with "which is which?". Now I declare my variables as creative misspellings of row and col (usually either rrow/ccol or rowy/colx) to keep myself straight.

Didn't know that trick with dropping an array variable into a range of cells as one big drop, that will speed up some of my massive macro workbooks, thanks for that!

I've gotten sort of used to x/y, at least until it falls out of me brain (probably tomorrow), but rowy/colx seems neat too! You are certainly welome of course

Mark

Last edited:
Thank you Mark,
Just what I was looking for...

You are certainly most welcome and glad that worked for you .

Replies
5
Views
505
Replies
9
Views
543
Replies
12
Views
901
Replies
1
Views
326
Replies
2
Views
258

1,221,153
Messages
6,158,228
Members
451,477
Latest member
CWebbers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back