VBA Next loop at the same time

Question

I need to have Next tab_name and Next j at the same time. Is there any way to do this?

tab_names = Array("11EB", "11WB", "12EB", "12WB", "13EB", "13WB", "14EB", "14WB", "15NB", "15SB", "16NB", "16SB", "17EB", "17WB", "18EB", "18WB", "19NB", "19SB", "20NB", "20SB", "21NB", "21SB", "22NB", "22SB", "23NB", "23SB", "24NB", "24SB", "25NB", "25SB", "26NB", "26SB", "27EB", "27WB", "28EB", "28WB", "29EB", "29WB", "30EB", "30WB", "31NB", "31SB", "32NB", "32SB", "33EB", "33WB", "34EB", "34WB", "35NB", "35SB", "36NB", "36SB", "37EB", "37WB", "38NB", "38SB", "39NB", "39SB", "40EB", "40WB", "41EB", "41WB", "A12NB", "A12SB", "M11NB", "M11SB", "M25NB", "M25SB", "A120EB", "A120WB", "A120AEB", "A120AWB")
   For i = 9 To 24
    For Each indiv_tab In tab_names
     For j = 3 To 291 Step 4

        Sheets("Front Page").Cells(2, 2) = Cells(i, 1)

        Cells(i, j) = Sheets(indiv_tab).Cells(2993, 9)
        Cells(i, j + 1) = Sheets(indiv_tab).Cells(2993, 22)
        Cells(i, j + 2) = Sheets(indiv_tab).Cells(2993, 35)
        Cells(i, j + 3) = Sheets(indiv_tab).Cells(2993, 48)
     Next j
   Next
 Next i

Not clear what you're trying to achieve. Please edit your question to describe exactly what the problem is with you code: desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. See: How to create a Minimal, Complete, and Verifiable example. — Jean-François Corbett
– Jean-François Corbett, Commented Apr 22, 2015 at 13:06
Reach array's elements with an index which is being increnmented in the for loop instead of for each. — kitap mitap
– kitap mitap, Commented Apr 22, 2015 at 13:13

kitap mitap · Accepted Answer · 2015-04-22 13:15:24Z

1

Reach array's elements with an index. I assume you want to increment index with every loop of inner for loop.

tab_names = Array("11EB", "11WB", "12EB", "12WB", "13EB", "13WB", "14EB", "14WB", "15NB", "15SB", "16NB", "16SB", "17EB", "17WB", "18EB", "18WB", "19NB", "19SB", "20NB", "20SB", "21NB", "21SB", "22NB", "22SB", "23NB", "23SB", "24NB", "24SB", "25NB", "25SB", "26NB", "26SB", "27EB", "27WB", "28EB", "28WB", "29EB", "29WB", "30EB", "30WB", "31NB", "31SB", "32NB", "32SB", "33EB", "33WB", "34EB", "34WB", "35NB", "35SB", "36NB", "36SB", "37EB", "37WB", "38NB", "38SB", "39NB", "39SB", "40EB", "40WB", "41EB", "41WB", "A12NB", "A12SB", "M11NB", "M11SB", "M25NB", "M25SB", "A120EB", "A120WB", "A120AEB", "A120AWB")
   For i = 9 To 24
     For j = 3 To 291 Step 4

        Sheets("Front Page").Cells(2, 2) = Cells(i, 1)

        Cells(i, j) = Sheets(tab_names((j-3)/4)).Cells(2993, 9)
        Cells(i, j + 1) = Sheets(tab_names((j-3)/4)).Cells(2993, 22)
        Cells(i, j + 2) = Sheets(tab_names((j-3)/4)).Cells(2993, 35)
        Cells(i, j + 3) = Sheets(tab_names((j-3)/4)).Cells(2993, 48)
     Next j
 Next i

answered Apr 22, 2015 at 13:15

kitap mitap

7098 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hc91 Over a year ago

Ahh, I see. There isn't a way of doing two of Next j and Next tab_name so you include them within the loop. Thanks.

Collectives™ on Stack Overflow

VBA Next loop at the same time

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related