CODEWITHSUNDEEP
vbaexcel
momchenr
momchenr

Reputation: 275

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

Upvotes: 0

Views: 1667

Answers (5)

Tim Sands
Tim Sands

Reputation: 1068

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

Upvotes: 0

Bigtree
Bigtree

Reputation: 199

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

Upvotes: 0

user3056839
user3056839

Reputation: 478

    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

Upvotes: 1

hstay
hstay

Reputation: 1439

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

Upvotes: 0

Excellll
Excellll

Reputation: 5785

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)

Upvotes: 1

Related Questions