Search code examples
excelvbaloopsdoublesubscript

Double For loops in VBA, different subscripts

I've got a problem with nested loop in VBA. Algorithm that I need to execute looks like this:

Option base 1
arr(4,2)
i = 1 To 4
j = 2 To 100

i = 1, j = 2 If Cells(2, 1).Value = arr(1,1) Then
                Cells(2,11) = arr(1,2)
i = 1, j = 3 If Cells(3,1).Value = arr(1,1) Then
                Cells(3,11) = arr(1,2)
i = 1, j = 4 If Cells(4,1).Value = arr(1,1) Then
                Cells(4,11).Value = arr(1,2) 
.
.
.

i = 1, j = 100 If Cells(100,1).Value = arr(1,1) Then
                  Cells(100,11).Value = arr(1,2)

And if j reaches 100 then

i = 2, j = 2 If Cells(2,1).Value = arr(2,1) Then
                Cells(2,11).Value = arr(2,2)
i = 2, j = 3 If Cells(3,1).Value = arr(2,1) Then
                Cells(3,11).Value = arr(2,2)
i = 2, j = 4 If Cells(4,1).Value = arr(2,1) Then
                Cells(4,11).Value = arr(2,2)
.
.
.

i = 2, j = 100 If Cells(100,1).Value = arr(2,1) Then
                  Cells(100,1).Value = arr(2,2)

And so on, until i = 4. I hope you get the idea :D Now I need to execute this in VBA - right now I have no idea how to do it, all I tried was a failure unfortunately, so I don't even have a code sample for you ;/ I tried things like

For i = 1 To 4
    For j = 1 To 100
      If Cells(j, 1).Value = arr(i, 1) Then
         Cells(j,11).Value = arr(i,2)
      End If
    Next j
Next i

But of course it showed "subscript out of range", I also tried something with For Each, but the same problem...

Please help :D

Solution

  • Based on your attempt above you need a Worksheet object to use the .Cells. See below:

    For i = 1 To 4
    For j = 1 To 100
        If Sheets("mySheet").Cells(j, 1).Value = arr(i, 1) Then
            Sheets("mySheet").Cells(j, 11).Value = arr(i, 2)
        End If
    Next j
Next i

    If you still run into subscript out of range look at your array dimensions as you may need to declare it to take into account arr(4,1) and arr(4,2).

    More information on the .Cells property here:

    https://learn.microsoft.com/en-us/office/vba/api/excel.range.cells