Multi for Array loop

[aka 42]

Hi guys,

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

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

 

[GTO]

Greetings AKA,

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

Mark

 

[aka 42]

Hi Mark,

Thanks fot the swift reply.

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.

Thanks for your help.

 

[GTO]

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

 

[aka 42]

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

 

[GTO]

Ahh, got it! Try:

In a Standard Module:

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

 

[gardnertoo]

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!

 

[GTO]

@aka 42:

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

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

 

[aka 42]

Thank you Mark,
Just what I was looking for...

 

[GTO]

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