Using Sequences In Loops?

[Lewzerrrr]

Hi,

Suppose i’m using a loop to enter values into certain cells but the positioning of these have a sequence. For example, I’m trying to put values in C5, E5, G5, I5, C10, E10, G10, I10 etc, what I’ve done is add 2 helper columns that hold the row indexes and columns indexes e.g. rows 5, 5, 5, 5, 10, 10, 10, 10 and columns 3, 5, 7, 9, 3, 5, 7, 9.

Whats the method to store these in VBA rather than helper columns incase they got deleted?

Is it something like and referencing through this? Not entirely sure. If you need more clarity then let me know.
Dim ArrRow(5,5,5,5,10,10,10,10) as string, ArrCol(3,5,7,9,3,5,7,9) as string


My code goes along the lines of..

Set RNG = Sheets(1).Range(“A2:A21”)

For Each c in RNG

StartRow = c.offset(,-6) ‘where helper row index is stored
StartCol = c.offset(,7) ‘where helper column index is stored

’code inserted here using .cells(StartRow, StartCol)

Next c

 

[Marcelo Branco]

Which value(s) are you trying to put in the cells?

To put the same value in all cells in rows 5, 10, 15 and 20, for example, try something like this

Sub aTest()
    Dim lRow As Long, lCol As Long
    
    For lRow = 5 To 20 Step 5
        For lCol = 3 To 9 Step 2
            Cells(lRow, lCol) = "MyValue"
        Next lCol
    Next lRow
        
End Sub

M.

 

[Lewzerrrr]

It’s more I’m importing pictures into the cells, however the cells COULD be random and may not follow a 2 To 20 Step 5
Also, there’s a sequence of XXXXYYYYZZZZ which also would fail a step?

 

[Marcelo Branco]

If the cells can be randomly located i don't know how to create a code.
Don't understand what you mean by a sequence XXXXYYYYZZZZ

M.

 

[Lewzerrrr]

If the cells can be randomly located i don't know how to create a code.
Don't understand what you mean by a sequence XXXXYYYYZZZZ

M.

So for every loop I need this sequence 4,4,4,4,7,7,7,7,8,8,8,8,11,11,11,11 and also maybe 4,7,10,11,4,7,10,11,4,7,10,11?

See the pattern? It’s XXXXYYYYZZZZWWWW and XYZWXYZWXYZWXYZW

 

[Marcelo Branco]

This?
Assumes values in rows 5, 10, 15, 20 in columns C, E, G and I

Sub aTest()
    Dim lRow As Long, lCol As Long
    Dim Arr As Variant, i As Long
    
    Arr = Array(4, 7, 8, 11)
    
    For lRow = 5 To 20 Step 5
        For lCol = 3 To 9 Step 2
             Cells(lRow, lCol) = Arr(i)
        Next lCol
        i = i + 1
    Next lRow
End Sub

M.

 

[Lewzerrrr]

This?
Assumes values in rows 5, 10, 15, 20 in columns C, E, G and I

Sub aTest()
    Dim lRow As Long, lCol As Long
    Dim Arr As Variant, i As Long
    
    Arr = Array(4, 7, 8, 11)
    
    For lRow = 5 To 20 Step 5
        For lCol = 3 To 9 Step 2
             Cells(lRow, lCol) = Arr(i)
        Next lCol
        i = i + 1
    Next lRow
End Sub

M.

Sorry to be a pain but the picture position doesn’t run in an order of steps, the only pattern would be a repeat sequence, hence the xxxxyyyyzzzzwwww for rows and xyzwxyzw for columns.

Apologies if this actually works, I’m just assuming it would need a different method to work. I’ll try to run this code tomorrow to see what it does though

 

[Marcelo Branco]

No need of apologies. Probably i'm not understanding what you really need.

By the way, the code above puts 4 in C5, E5, G5, H5; 7 in C10, E10, G10, H10 and so on

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[Lewzerrrr]

No need of apologies. Probably i'm not understanding what you really need.

By the way, the code above puts 4 in C5, E5, G5, H5; 7 in C10, E10, G10, H10 and so on

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

Looks good but what if it doesn’t follow the same gap?

E.G C, F, G, I and 5, 7, 11, 12?

 

[Marcelo Branco]

Maybe...

Say you want the values (sequences) 5, 7, 11 and 12 in rows 2, 4, 5, 9 in columns 3, 4, 7, 9 (random rows/columns)

Try

Sub aTest()
    Dim arrCols As Variant, arrRows As Variant
    Dim lRow As Long, lCol As Long
    Dim arrVals As Variant, i As Long
   
    arrVals = Array(5, 7, 11, 12)
    arrRows = Array(2, 4, 5, 9)
    arrCols = Array(3, 4, 7, 9)
    
    For lRow = LBound(arrRows) To UBound(arrRows)
        For lCol = LBound(arrCols) To UBound(arrCols)
            Cells(arrRows(lRow), arrCols(lCol)) = arrVals(i)
        Next lCol
        i = i + 1
    Next lRow
End Sub

Result

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

11​

[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

12​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD]

12​

[/TD]
[/TR]
</tbody>[/TABLE]

Is this ok?

M.