AW: Aufsummieren
29.03.2023 08:19:14
Ron
Hallo Daniel,
ich habe deinen Code eingearbeitet. Die Summe wird zwar gebildet aber immer mal 5. ???
Gruß
Private Sub CommandButton1_Click()
Dim filePath As String
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Dim yearLabel As String
Dim yearFound As Boolean
Dim yearValue As Integer
Dim sumValue As Double
Dim Summe19 As Double
Dim Summe16 As Double
Dim Zeilen As Long
'Öffnen des Dialogfensters zum Auswählen der Datei
With Application.fileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls"
.AllowMultiSelect = False
If .Show = True Then
filePath = .SelectedItems(1)
Else
Exit Sub
End If
End With
'Öffnen der ausgewählten Datei
Set wb = Workbooks.Open(filePath)
Set ws = wb.Sheets(1)
'Prüfen, ob in den Labels vierstellige Jahre stehen
For i = 1 To 5
yearLabel = Replace(Me.Controls("Label" & i).Caption, "Jahr ", "")
yearFound = False
If Len(yearLabel) = 4 And IsNumeric(yearLabel) Then
yearValue = CInt(yearLabel)
Zeilen = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - 69
With ws.Cells(70, 1).Resize(Zeilen, 6)
For j = 1 To 5
yearLabel = Replace(Me.Controls("Label" & j).Caption, "Jahr ", "")
Summe19 = WorksheetFunction.SumIfs(.Columns(6), .Columns(1), yearLabel, .Columns(5), 19)
Summe16 = Summe16 + WorksheetFunction.SumIfs(.Columns(6), .Columns(1), yearLabel, .Columns(5), 16)
With Me.Controls("Textbox" & j)
If .Text = "" Then
.Text = CStr(Summe19)
Else
.Text = CStr(CDbl(.Text) + Summe19)
End If
End With
Next
With TextBox6
If .Text = "" Then
.Text = CStr(Summe16)
Else
.Text = CStr(CDbl(.Text) + Summe16)
End If
End With
End With
End If
Next i
'Beenden des Makros
wb.Close SaveChanges:=False
Set ws = Nothing
Set wb = Nothing
End Sub