Kombinationen auflisten mit Spaltenüberschrift

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Kombinationen auflisten mit Spaltenüberschrift
von: Hornung
Geschrieben am: 16.06.2015 21:45:15

Hallo zusammen,
ich möchte gerne alle möglichen Kombinationen mehrerer Spalten sowie Zeilen ausgeben.
Dafür nutze ich folgenden Code:

Public Sub KombinationenAusgeben()
Dim a, b, c, d, S, lngCount As Long
Dim I As Integer
Dim out
Dim Z
Z = 1
S = Sheets("Tabelle2").Range("A1").CurrentRegion.Columns.Count
 
ReDim spalten(1 To S) As Range
For I = 1 To S
    Set spalten(I) = Range(Cells(Rows.Count, I).End(xlUp), Cells(2, I))
    Z = Z * spalten(I).Count
Next
If Z > 65536 Then
    MsgBox "Mehr Kombinationen möglich als Zeilen vorhanden.", vbCritical, "Problem"
    Exit Sub
End If
On Error Resume Next
ReDim out(1 To Z, 1 To 4)
For a = 1 To spalten(1).Count
    For b = 1 To spalten(2).Count
        For c = 1 To spalten(3).Count
            For d = 1 To spalten(4).Count
            
                                lngCount = lngCount + 1
                                out(lngCount, 1) = spalten(1)(a)
                                out(lngCount, 2) = spalten(2)(b)
                                out(lngCount, 3) = spalten(3)(c)
                                out(lngCount, 4) = spalten(4)(d)
                              
                            Next
                        Next
                    Next
                Next
Sheets("Tabelle4").Range("A2").Resize(UBound(out), UBound(out, 2)) = out
End Sub


Ich möchte/brauche allerdings je Spalte eine Spaltenüberschrift (in der ersten Zeile). Hierfür habe ich im Code die 2 bei "Cells()" eingefügt.
Set spalten(I) = Range(Cells(Rows.Count, I).End(xlUp), Cells(2, I))
Leider funktioniert das nur dann, wenn unterhalb der Spaltenüberschrift Werte stehen.
Sind unterhalb der Spaltenüberschrift keine Werte eingetragen, dann nimmt er nicht ausschließlich die entsprechenden Werte zur Kombinationsbildung her, sondern auch die Spaltenüberschrift, also z.B.

Müller Michael Alter
Müller Max Alter

Sind hingegen Werte unter der Überschrift eingetragen, dann funktioniert die Kombination wie gewollt,
also

Müller Michael 26
Müller Max 26

(ich brauche für den nächsten Schritt in meinem Excel-VBA-Code eine solche Konstellation mit Spaltenüberschrift unterhalb der keine Werte stehen)
Kann mir jemand sagen, was das Problem an meinem Code ist bzw. warum es nicht funktioniert?
Danke schon einmal im Voraus!
Gruß Marcel

Bild

Betrifft: AW: Kombinationen auflisten mit Spaltenüberschrift
von: fcs
Geschrieben am: 19.06.2015 04:04:00
Hallo Marcel,
in
Set spalten(I) = Range(Cells(Rows.Count, I).End(xlUp), Cells(2, I))
liefert
Cells(Rows.Count, I).End(xlUp)
in einer leeren Spalte oder wenn nur Wert in Zeile 1 immer die Zelle in Zeile 1 der Spalte.
Wenn immer in Zeile 2 mit dem Bereich begonnen werden soll, dann muss du die letzte Datenzeile in der Spalte prüfen.

a = Cells(Rows.Count, I).End(xlUp).Row
If a < 2 Then
    Set spalten(I) = Range(Cells(2, I), Cells(2, I))
Else
    Set spalten(I) = Range(Cells(2, I), Cells(a, I))
End If
Bei der Berechnung für das out-Array für diese Spalte dann eine Serie von Kombinationen mit Leerwerten erzeugt.
Gruß
Franz

Bild

Betrifft: AW: Kombinationen auflisten mit Spaltenüberschrift
von: Hornung
Geschrieben am: 19.06.2015 18:55:32
Super, danke dir Franz! Das hilft mir enorm weiter!
Viele Grüße
Marcel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Kombinationen auflisten mit Spaltenüberschrift"