PDA

View Full Version : [SOLVED] Array Step by 50 - Loop Incremental



dj44
11-24-2017, 01:45 PM
folks good day,

is there a way to make another shortcut loop to go wth my array

as you can see

i would like to step by 50

so i did the multiplication





Dim n: n = 50

oDestination = Array("A1", "A" & n, "A" & 2 * n, "A" & 3 * n, "A" & 4 * n, "A" & 5 * n, "A" & 6 * n, "A" & 7 * n, "A" & 8 * n, "A" & 9 * n)



but it does look very confusing to the layman

with my A's and n's

so the result is actually meant to be this in the array ("A1","A50",A100,A150....")

these are my cell ranges

Paul_Hossler
11-24-2017, 02:17 PM
Try this

A1 requires some special handling





Option Explicit

Sub BuildArray()
Dim i As Long
Dim s As String
Dim oDestination As Variant

s = "A1;"

For i = 50 To 450 Step 50
s = s & "A" & i & ";"
Next i
s = Left(s, Len(s) - 1)

oDestination = Split(s, ";")
Stop

dj44
11-24-2017, 02:45 PM
Well all i can say Paul
is you are some sort of genuis.

And you may be able to read minds too, i forgot to mention the partner array but you knew somehow.

I had 2 arrays

this one being the destination with the 50 increment and another one.

Well this line

oDestination = Array("A1", "A" & n, "A" & 2 * n, "A" & 3 * n, "A" & 4 * n, "A" & 5 * n, "A" & 6 * n, "A" & 7 * n, "A" & 8 * n, "A" & 9 * n, "A" & 10 * n, "A" & 11 * n) 'n*2 , n*3

was only meant to be small but it got bigger as i had more ranges, now dont ask me how these things happen :grinhalo:

Suffice to say i am very happy with this new step loop it will come in very handy so i dont have to type all those multiplications, and i spent half a day not understing a nested while loop so i gave up

well thank you very much Paul

and a cracking friday and great weekend

snb
11-24-2017, 03:16 PM
Or


Sub M_snb()
Range(Join([transpose("A"&50*row(1:9))], ",")).Interior.ColorIndex = 3
End Sub

dj44
11-24-2017, 03:43 PM
Thank you SNB,

i will play about with this as well :)