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]

Or if you want this


[Table="class: grid"][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]

7​

[/td][td][/td][td][/td][td]

11​

[/td][td][/td][td]

12​

[/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]

5​

[/td][td]

7​

[/td][td][/td][td][/td][td]

11​

[/td][td][/td][td]

12​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

5​

[/td][td]

7​

[/td][td][/td][td][/td][td]

11​

[/td][td][/td][td]

12​

[/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]

5​

[/td][td]

7​

[/td][td][/td][td][/td][td]

11​

[/td][td][/td][td]

12​

[/td][/tr]
[/table]

Try

Sub aaTest()
    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)
        i = 0
        For lCol = LBound(arrCols) To UBound(arrCols)
            Cells(arrRows(lRow), arrCols(lCol)) = arrVals(i)
            i = i + 1
        Next lCol
    Next lRow
End Sub

M.

 

[Lewzerrrr]

Or if you want this


[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]

7​

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

11​

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

12​

[/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]

5​

[/TD]
[TD]

7​

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

11​

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

12​

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

5​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

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

11​

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

12​

[/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]

5​

[/TD]
[TD]

7​

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

11​

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

12​

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


Try

Sub aaTest()
    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)
        i = 0
        For lCol = LBound(arrCols) To UBound(arrCols)
            Cells(arrRows(lRow), arrCols(lCol)) = arrVals(i)
            i = i + 1
        Next lCol
    Next lRow
End Sub

M.

Perfect! I’m sure I can tailor this to my code I assumed this would be the way but never used arrays!

Thanks

 

[Lewzerrrr]

Or if you want this


[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]

7​

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

11​

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

12​

[/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]

5​

[/TD]
[TD]

7​

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

11​

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

12​

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

5​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

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

11​

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

12​

[/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]

5​

[/TD]
[TD]

7​

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

11​

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

12​

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


Try

Sub aaTest()
    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)
        i = 0
        For lCol = LBound(arrCols) To UBound(arrCols)
            Cells(arrRows(lRow), arrCols(lCol)) = arrVals(i)
            i = i + 1
        Next lCol
    Next lRow
End Sub

M.

Scrap that! Unsure how to really do it lol! See my full code.. If you can help me understand the method to incorporate arrays into this that would be appreciated!

Set wsMaster = Sheets("Master")Set wsTop = Sheets("Top 20 Bestsellers")


Set ListOfLines = wsMaster.Range("C3:C22")


'-- Pics need to be inserted into (7, 3) (7, 7) (7, 11) (7, 15) (23, 3) (23, 7) ...
arrRows = Array(7, 23, 39, 55, 71)
arrCols = Array(3, 7, 11, 15)


Application.ScreenUpdating = False


wsTop.Activate


For Each LineNo In ListOfLines


StartRow = LineNo.Offset(, 6) '-- Helper column / array of numbers are stored in wsMaster.Range("I3:I22")
StartCol = LineNo.Offset(, 7) '-- Helper column / array of numbers are stored in wsMaster.Range("J3:J22")


URL = "http://media.topshop.com/wcsstore/TopShop/images/catalog/TS" & LineNo & "_Large_F_1.jpg"


    With wsTop.Cells(StartRow, StartCol)
    
        On Error Resume Next
            .Select
                Set pic = .Parent.Pictures.Insert(URL)
                    
                    With pic
                        .Top = pic.Top + 15
                        .Left = pic.Left + 32
                        .Width = 120
                        .Height = 130
                    End With
                    
                Set pic = Nothing
                
    End With


Next LineNo

 

[Marcelo Branco]

There are many posts in the internet about: VBA How to insert a picture in a cell.
Try Google - i've made a quick search and...
https://stackoverflow.com/questions...o-excel-at-a-specified-cell-position-with-vba
http://ccm.net/faq/6485-excel-a-macro-to-insert-pictures
You can find many others

M.

 

[Lewzerrrr]

There are many posts in the internet about: VBA How to insert a picture in a cell.
Try Google - i've made a quick search and...
https://stackoverflow.com/questions...o-excel-at-a-specified-cell-position-with-vba
http://ccm.net/faq/6485-excel-a-macro-to-insert-pictures
You can find many others

M.

Thanks Marcelo,

I figured it out using the following, works perfectly so far! Thanks for all your help and putting me in the right direction.

Sub InsertPics2()

Dim wsMaster As Worksheet, wsTop As Worksheet
Dim arrRows As Variant, arrCols As Variant
Dim lRow As Long, lCol As Long
Dim LineNo(19) As String
Dim I As Long


Set wsMaster = Sheets("Master")
Set wsTop = Sheets("Top 20 Bestsellers")


arrRows = Array(7, 23, 39, 55, 71)
arrCols = Array(3, 7, 11, 15)


For I = 0 To 19
    LineNo(I) = wsMaster.Range("C" & I + 3)
Next I


Application.ScreenUpdating = False


wsTop.Activate


    I = 0
    For lRow = LBound(arrRows) To UBound(arrRows)
        For lCol = LBound(arrCols) To UBound(arrCols)
            URL = "http://media.topshop.com/wcsstore/TopShop/images/catalog/TS" & LineNo(I) & "_Large_F_1.jpg"
                With wsTop.Cells(arrRows(lRow), arrCols(lCol))
                    On Error Resume Next
                        .Select
                            Set pic = .Parent.Pictures.Insert(URL)
                                With pic
                                    .Top = pic.Top + 15
                                    .Left = pic.Left + 32
                                    .Width = 120
                                    .Height = 130
                                End With
                            Set pic = Nothing
                End With
            I = I + 1
        Next lCol
    Next lRow
    
Erase LineNo


wsTop.[A1].Select


Set wsMaster = Nothing
Set wsTop = Nothing
Set ListOfLines = Nothing


Application.ScreenUpdating = True


End Sub

 

[Marcelo Branco]

Great you solved your problem!

Below, a code i created to insert 4 different pictures whose location is defined by arrRows and arrCols (just an example)

Sub aTest()
    'This code inserts 4 pictures in cells whose location is defined by two arrays
    Dim arrCols As Variant, arrRows As Variant
    Dim lRow As Long, lCol As Long
    Dim strPath(0 To 3) As String, i As Long, rCell As Range
   
    'Array of pictures' paths - Adjust to suit
    strPath(0) = "C:\Users\Marcelo\Documents\IMGS\Apcb1.jpg"
    strPath(1) = "C:\Users\Marcelo\Documents\IMGS\Ava1.jpg"
    strPath(2) = "C:\Users\Marcelo\Documents\IMGS\Ava5.jpg"
    strPath(3) = "C:\Users\Marcelo\Documents\IMGS\Flu1.jpg"
    
    'Arrays of rows and columns
    arrRows = Array(7, 23, 39, 55, 71)
    arrCols = Array(3, 7, 11, 15)
    
    For lRow = LBound(arrRows) To UBound(arrRows)
        i = 0
        For lCol = LBound(arrCols) To UBound(arrCols)
            Set rCell = Cells(arrRows(lRow), arrCols(lCol))
            Rows(rCell.Row).RowHeight = 31.5
             With ActiveSheet.Pictures.Insert(strPath(i))
                With .ShapeRange
                    .LockAspectRatio = msoTrue
                    .Width = 30
                    .Height = 30
                End With
                .Left = rCell.Left
                .Top = rCell.Top
                .Placement = 1
                .PrintObject = True
            End With
            i = i + 1
        Next lCol
    Next lRow
End Sub

M.