CODEWITHSUNDEEP
vbaexcel
hc91
hc91

Reputation: 773

VBA Next loop at the same time

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

Upvotes: 0

Views: 523

Answers (1)

kitap mitap
kitap mitap

Reputation: 709

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

Upvotes: 1

Related Questions