1. ## 32 elements

User Aidy Rutter (AidyR@TrustMarque.com.nospam) posted:

So I got a loop: x = 1 To 32, and an array has 32 elements which I
reference like this.

like this: strRange(1) = "B9:E9"
strRange(2) = "B11:E11"

.......

strRange(31) = "B72:E72"
strRange(32) = "B74:E74"

Do I have to write 32 lines of reference?

I can strRange(x), but I need to start the cell range at "B9:E9" and step up
by 2 , to 32. Could someone point me in the right direction?

Thanks

Aidy

---

2. ## Re: 32 elements

User (MMcNaughton@STSSystems.com.nospam) posted:

In a for loop use the optional parameter STEP to step by 2 and create your
cell references on the fly. Make sense?

Max

3. ## Re: 32 elements

User (MMcNaughton@STSSystems.com.nospam) posted:

Oh so you won't be able to use x in your array, you'll have to use another
counter for that

Max

4. ## Re: 32 elements

User Aidy Rutter (AidyR@TrustMarque.com.nospam) posted:

So if I create another counter

x =y and I need y to step by 2, and start at "B9:E9", but I only want the
numeric to increase by two.

I don't understand about references on the fly.

Cheers

Aidy

5. ## Re: 32 elements

User Shevchenko, Dmitry (DShevchenko@flagship.ru.nospam) posted:

Aidy,

try this:

Dim start, index As String

start="7"

For i=1 To 32
index=Trim\$(Str\$(Val(start)+i*2))
strRange(i)="B"+index+":E"+index
Next

But how can you get even index (at the end, see your message), if you start
with odd number and always add 2?

Dmitry

6. ## Re: 32 elements

User (MMcNaughton@STSSystems.com.nospam) posted:

Try something like the following, you'd have to test it but generally this
is the idea

Dim x as integer
Dim y as integer
Dim ChrSet as integer
Dim tempRange as string
Dim strRange(32)
y=1
ChrSet = 65 ' Chr(65) = A, Chr(66) = B etc...

For x = 1 to 32 step 2
tempRange = Chr(ChrSet) & x & ":" & Chr(ChrSet) & x
strRange(y) = tempRange
msgbox strRange(y)
y = y + 1
Next x

Max

7. ## Re: 32 elements

User Aidy Rutter (AidyR@TrustMarque.com.nospam) posted:

Dmitry, Max et al,

Thanks for all your help; they both work. I need to tidy up the scripts so I
can maintain them for future builds (and so the new team leader wont think
I'm a loser).

You may make a programmer of me yet.

Cheers

Aidy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.