# Nested Loops in VBA Excel

I have a need to create a list based on this data. I need it to render numbers 000000 through numbers 080808. Hoping to format the data like this...

000000 - CHW EQUIPMENT RTU

000001 - CHW EQUIPMENT CHILLER

Etc. I need a list of all potential combinations of numbers, with the description of what that would be. Like:

=concatenate(A1,C1,E1," - ",B1," ",D1," ",F1)

Except looped through all possible combinations. Can someone help me come up with a loop that would do this? I'm not that familiar with Macros.

A B C D E F 00 CHW 00 Equipment 00 RTU 01 HHW 01 SM Steel Pipe 01 Chiller 02 Steam 02 LB Steel Pipe 02 Split System 03 Med Gas 03 Copper 03 Pump 04 UG/Soil 04 Cast Iron 04 Boiler 05 Domestic Water 05 Plastic 05 Cooling Tower 06 Compressed Air 06 Double-Wall 06 Air Compressor 07 Natural Gas 07 Sheetmetal 07 Fan 08 Refrigerant 08 Fixtures 08 Unit Vent

## Answers

Sub GetAllCombinations() Dim rowNum As Integer Dim rowValue As String Dim celRange As String Dim A As String Dim B As String Dim C As String Dim D As String Dim E As String Dim F As String rowNum = 1 For Each cell In Range("A2:A10") A = Range("A" + CStr(cell.Row)).Value B = Range("B" + CStr(cell.Row)).Value For Each cell2 In Range("C2:C10") C = Range("C" + CStr(cell2.Row)).Value D = Range("D" + CStr(cell2.Row)).Value For Each cell3 In Range("E2:E10") E = Range("E" + CStr(cell3.Row)).Value F = Range("F" + CStr(cell3.Row)).Value Range("H" + CStr(rowNum)).Value = A + C + E + " - " + B + D + F rowNum = rowNum + 1 Next Next Next End Sub

Since you say you're unfamiliar with VBA, here is a worksheet function solution. Just enter in row 1 of a column and fill down to row 729:

=INDEX($A$1:$A$9,INT((ROW()-1)/81)+1)&INDEX($C$1:$C$9,MOD(INT((ROW()-1)/9),9)+1)&INDEX($E$1:$E$9,MOD(ROW()-1,9)+1)&" - "&INDEX($B$1:$B$9,INT(ROW()/81)+1)&" "&INDEX($D$1:$D$9,MOD(INT((ROW()-1)/9),9)+1)&" "&INDEX($F$1:$F$9,MOD(ROW()-1,9)+1)

I agree with @ARich comment, but since I cannot resist myself here it is the code:

Sub CombineList() Dim i As Integer Dim Cell1 As Range, Cell2 As Range, Cell3 As Range i = 0 For Each Cell1 In Range("A2:A10") For Each Cell2 In Range("C2:C10") For Each Cell3 In Range("E2:E10") i = i + 1 Range("G" & i + 1).Value = CStr(Cell1.Value) & (Cell2.Value) & (Cell3.Value) _ & " - " & CStr(Cell1.Offset(0, 1).Value) & " " & (Cell2.Offset(0, 1).Value) & _ " " & (Cell3.Offset(0, 1).Value) Next Cell3 Next Cell2 Next Cell1 End Sub

I made one too...for what it's worth

Sub Create_list() 'Declare Variables Dim a As Integer Dim b As Integer Dim c As Integer Dim ws1lastrow As Integer Dim ws2lastrow As Integer Dim ws1 As Worksheet Dim ws2 As Worksheet 'Set Variables Set ws1 = ThisWorkbook.Sheets("sheet1") Set ws2 = ThisWorkbook.Sheets("sheet2") ws1lastrow = ws1.UsedRange.Rows.Count ws2lastrow = 2 'Start at row 2 to leave room for header 'The three loops For a = 2 To ws1lastrow 'Again start at row 2 For b = 2 To ws1lastrow For c = 2 To ws1lastrow 'Build the index number ws2.Cells(ws2lastrow, 1).Value = "'" & Format(ws1.Cells(a, 1).Value, "00") & Format(ws1.Cells(b, 3).Value, "00") & Format(ws1.Cells(c, 5).Value, "00") 'Build the value ws2.Cells(ws2lastrow, 2).Value = ws1.Cells(a, 2).Value & " " & ws1.Cells(b, 4).Value & " " & ws1.Cells(c, 6).Value 'advance ws2's row counter ws2lastrow = ws2lastrow + 1 Next c Next b Next a End Sub

Heres a general loop to get all combos of ranges. This method is extensible to more ranges if desired and I've created a sample workbook here

Sub AllCombos() Dim rngA As Range: Dim rngB As Range: Dim rngC As Range Dim vaResults() Dim ws As Worksheet 'User set references to ranges - they can be different sizes Set rngA = Range("A2:A10") Set rngB = Range("C2:C10") Set rngC = Range("E2:E10") 'Determine total number of combos that will be created iComboCount = rngA.Count * rngB.Count * rngC.Count ReDim vaResults(1 To iComboCount, 1 To 1) 'Loop through all combos of indices for ranges '---------------------------------------------- cnt = 0: aIndex = 0: bIndex = 0: cIndex = 0 Do Until cnt >= iComboCount cIndex = (cnt Mod rngC.Count) + 1 'if cIndex started over, add 1 to bIndex If cIndex = 1 Then bIndex = bIndex Mod rngB.Count + 1 'if bIndex AND cIndex started over, add 1 to aIndex If (bIndex * cIndex) = 1 Then aIndex = aIndex Mod rngA.Count + 1 'etc... if more columns needed 'Now we have all indices, save the desired value in VBA array vaResults(cnt + 1, 1) = "'" & _ rngA.Cells(aIndex, 1) & rngB.Cells(bIndex, 1) & rngC.Cells(cIndex, 1) & " - " & _ rngA.Cells(aIndex, 2) & rngB.Cells(bIndex, 2) & rngC.Cells(cIndex, 2) cnt = cnt + 1 Loop 'Output the result array to excel '---------------------------------- Set ws = Worksheets.Add 'Make an output range that is the same size as VBA array Set rngOutput = ws.Cells(1, 1).Resize(UBound(vaResults, 1), UBound(vaResults, 2)) 'Copy the VBA array to Excel rngOutput.Value = vaResults MsgBox "All combos created" End Sub