Sub AlleRechnungenErstellen()

    Dim members As Collection
    Set members = New Collection

    Dim memberAddrs As Object
    Set memberAddrs = CreateObject("Scripting.Dictionary")

    Dim row, row2 As Long
    Dim maxRow, maxRow2 As Long
    Dim workbooks2 As Object
    Dim workbooks3 As Object
    Dim sheet, sheet2, sheet3 As Worksheet

    'Sheet2 ist das Auftragsverzeichnis

    Set sheet2 = ActiveWorkbook.Worksheets(1)
    maxRow = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp).row

    'Fenster öffnet sich um Pfad zur Redmine.xlsx abzufragen

    Dim intChoice As Integer
    Dim strPath As String

    'Es darf nur eine Datei vom Benutzer ausgewält werden
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    'File Dialog sichtbar machen für Benutzer
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'Abfrage welche Auswahl der Benutzer getroffen hat
    If intChoice <> 0 Then
    'Speicher Filepath den der Benutzer ausgewählt hat als String
    strPath = Application.FileDialog( _
    msoFileDialogOpen).SelectedItems(1)

    End If



    'Workbooks2 = Redmine Datei
    Set workbooks2 = Workbooks.Open(strPath)

    Set sheet = workbooks2.Worksheets(1)

    maxRow2 = sheet.Cells(sheet.Rows.Count, "A").End(xlUp).row


    Dim answer As Integer
    Dim language As Integer
    Dim answer2 As Integer
    Dim answer3 As Integer
    Dim curName As String
    Dim curHour As Double
    Dim curAddr As String
    Dim curName2 As String
    Dim curAddr2 As String
    Dim curAddressed As String
    Dim curNr As String
    Dim curAnrede As String
    Dim curPLZ As String
    Dim curOrt As String
    Dim curAuftragsDatum As String
    Dim curStundensatz As Integer
    Dim RechnungsSumme As Double
    Dim vormonat As Double
    Dim Speicher As String


    For row = 2 To maxRow2  'Kommazahlen in Zahl mit Punkt umwandeln
    Speicher = sheet.Cells(row, 7)
    Speicher = Replace(Speicher, ",", ".")
    sheet.Cells(row, 7).Value = Speicher
    Next row



    For row2 = maxRow To 3 Step -1  'Läuft von unten nach oben
        curName2 = sheet2.Cells(row2, 2)
        curAddr2 = sheet2.Cells(row2, 3)
        curAddressed = sheet2.Cells(row2, 6)
        vormonat = sheet2.Cells(row2, 22)



        If IsNumeric(sheet2.Cells(row2, 12)) Then
        curStundensatz = sheet2.Cells(row2, 12)
        End If

        curAnrede = sheet2.Cells(row2, 5)
        If InStr(1, sheet2.Cells(row2, 4), " ") Then
            curPLZ = Split(sheet2.Cells(row2, 4), " ")(0)
            curOrt = Split(sheet2.Cells(row2, 4), " ")(1)
        End If

        curAuftragsDatum = sheet2.Cells(row2, 14)

        If Not memberAddrs.Exists(curName2) Then
            Set newMember = New auftrag
            Call newMember.Create(curName2, 0, curAddr2, curAddressed, curAnrede, curPLZ, curOrt, curAuftragsDatum, curStundensatz, vormonat)
            Call members.Add(newMember)
            Call memberAddrs.Add(curName2, curAddr2)
        End If

    Next row2



    For row = 2 To maxRow2

        curName = sheet.Cells(row, 1)
        curAddr = memberAddrs(curName)
        curHour = Val(sheet.Cells(row, 7))



        If MemberExist(members, curName) Then

            Set checkMember = GetMemberByName(members, curName)
            checkMember.AddHours (Val(curHour))
       Else

        Set newMember = New auftrag
        Call newMember.Create(curName, 0, curAddr, "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", 0, 0)
        Call members.Add(newMember)
        End If

    Next row

    Dim strEintrag As Integer
    strEintrag = InputBox("Tragen Sie die aktuelle Rechnungsnummer ein:")

    Dim member As auftrag
    Dim number As Integer
    number = 1






    For Each member In members




        If member.GetHours > 0 Then

        RechnungsSumme = (member.GetHours * member.GetStundensatz) + ((member.GetHours * member.GetStundensatz) / 100) * 19

        sheet.Cells(number, "H").Value = member.GetName
        number = number + 1

        MsgBox ("Name: " & member.GetName & vbNewLine & "Stunden: " & member.GetHours & vbNewLine & "Straße: " & member.GetAddr & vbNewLine & "PLZ: " & member.GetPLZ & vbNewLine & "Ort: " & member.GetOrt & vbNewLine & "Anrede: " & member.GetAnrede & vbNewLine & "Ansprechpartner: " & member.GetAddressed & vbNewLine & "Stundensatz: " & member.GetStundensatz & vbNewLine & "Rechnungssumme: " & RechnungsSumme & vbNewLine & "Stunden Vormonat: " & member.GetStundenVormonat)




        End If




    Next member





    UserForm1.Show

End Sub
        Sub userformstarten()
   UserForm1.Show
End Sub








Private Function GetMemberByName(list As Collection, name As String) As auftrag
    Dim member As auftrag

    For Each member In list
        If member.GetName = name Then
            Set GetMemberByName = member
            Exit Function
        End If
    Next member
End Function
Private Function MemberExist(list As Collection, name As String) As Boolean
    Dim member As auftrag

    For Each member In list
        If member.GetName = name Then
            MemberExist = True
            Exit Function
        End If
    Next member

    MemberExist = False
End Function



_________________________________________________________________________________________________________-

KLASSENMODUL

Private M_name As String
Private M_hours As Double
Private M_addressed As String
Private M_addr As String
Private M_stundensatz As Integer
Private M_anrede As String
Private M_plz As String
Private M_ort As String
Private M_StundenVormonat As Double
Private M_auftragsDatum As String

Public Sub Create(ByVal name As String, ByVal hours As Double, ByVal addr As String, ByVal addressed As String, ByVal anrede As String, ByVal plz As String, ByVal ort As String, ByVal auftrag As String, ByVal stundensatz As Integer, ByVal stundenVormonat As Double)
    M_name = name
    M_hours = hours
    M_addr = addr
    M_addressed = addressed
    M_anrede = anrede
    M_plz = plz
    M_ort = ort
    M_auftragsDatum = auftrag
    M_stundensatz = stundensatz
    M_StundenVormonat = stundenVormonat

End Sub

Public Function GetStundensatz() As Integer
GetStundensatz = M_stundensatz
End Function
Public Function GetAnrede() As String
    GetAnrede = M_anrede
End Function
Public Function GetAuftragsdatum() As String
    GetAuftragsdatum = M_auftragsDatum
End Function
Public Function GetPLZ() As String
    GetPLZ = M_plz
End Function
Public Function GetOrt() As String
    GetOrt = M_ort
End Function
Public Function GetName() As String
    GetName = M_name
End Function
Public Function GetHours() As Double
    GetHours = M_hours
End Function
Public Function GetAddr() As String
    GetAddr = M_addr
End Function
Public Function GetStundenVormonat() As Double
    GetStundenVormonat = M_StundenVormonat
End Function
Public Function GetAddressed() As String
    GetAddressed = M_addressed
End Function

Public Sub AddHours(hours As Double)
    M_hours = M_hours + hours
End Sub

_________________________________________________________________________________________________________-

USERFORM

Private Sub CommandButton1_Click()

End Sub

Private Sub RechnungStellen_Click()
Dim member As auftrag
For i = 1 To 10
If Me.Controls("TextBox" & CStr(i)).Text <> "" Then
    If Me.Controls("CheckBox" & CStr(i)).Value = True Then
    MsgBox (Me.Controls("CheckBox" & CStr(i)).Value & " RECHNUNG WIRD ERSTELLT")
 'For Each member In members
 'If Me.Controls("CheckBox" & CStr(i)).Value = member.GetName Then

  '         MsgBox ("Member Existiert")
   '      End If

' Next member

End If
End If

Next
End Sub

Private Sub UserForm_Initialize()
  Dim i As Byte
  Dim currentName As String



    Dim workbooks2 As Object

    Dim sheet As Worksheet

     Dim intChoice As Integer
    Dim strPath As String

    'Es darf nur eine Datei vom Benutzer ausgewält werden
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    'File Dialog sichtbar machen für Benutzer
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'Abfrage welche Auswahl der Benutzer getroffen hat
    If intChoice <> 0 Then
    'Speicher Filepath den der Benutzer ausgewählt hat als String
    strPath = Application.FileDialog( _
    msoFileDialogOpen).SelectedItems(1)

    End If


    Set workbooks2 = Workbooks.Open(strPath)

    Set sheet = workbooks2.Worksheets(1)

  For i = 1 To 10
    '8 = H
    currentName = Cells(i, 8).Value
    UserForm1.Controls("TextBox" & CStr(i)).Text = currentName
  Next
End Sub