Hi,
(10.12.2022, 12:55)Egon12 schrieb: [ -> ]Naja, mittels Select Case spart man an dieser Front nur wenig, da ja die jeweiligen Zuweisungen (1 Befehlszeile) bleiben. Deshalb ist es so ziemlich egal, ob If/Else oder Select Case.
dem möchte ich doch ein wenig widersprechen.
Der bisherige Code mit
If Code:
Sub Datum1()
Dim i As Long, j As Long, k As Long, Spalte1 As Range, Spalte2 As Range
'Geburts Datum
i = ActiveCell.Row
With Tabelle1
Set Spalte1 = .Rows(6).Find("Geburt-Datum")
If Not Spalte1 Is Nothing Then k = Spalte1.Column
Set Spalte2 = .Rows(6).Find("Geburt.-.Datum")
If Not Spalte2 Is Nothing Then j = Spalte2.Column
If Len(.Cells(i, k)) = 4 Then .Cells(i, j) = .Cells(i, k)
If Len(.Cells(i, k)) = 7 Then
If Left(.Cells(i, k), 2) = "01" Then
.Cells(i, j) = "JAN " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "02" Then
.Cells(i, j) = "FEB " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "03" Then
.Cells(i, j) = "MRZ " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "04" Then
.Cells(i, j) = "APR " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "05" Then
.Cells(i, j) = "MAI " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "06" Then
.Cells(i, j) = "JUN " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "07" Then
.Cells(i, j) = "JUL " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "08" Then
.Cells(i, j) = "AUG " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "09" Then
.Cells(i, j) = "SEPT " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "10" Then
.Cells(i, j) = "OKT " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "11" Then
.Cells(i, j) = "NOV " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 2) = "12" Then
.Cells(i, j) = "DEZ " & Right(.Cells(i, k), 4)
End If
Else
If Mid(.Cells(i, k), 4, 2) = "01" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " JAN " & Right(.Cells(i, k), 4)
End If
End If
If Mid(.Cells(i, k), 4, 2) = "02" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " FEB " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "03" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " MRZ " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "04" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " APR " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "05" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " MAI " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "06" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " JUN " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "07" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " JUL " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "08" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " AUG " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "09" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " SEPT " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "10" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " OKT " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "11" Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " NOV " & Right(.Cells(i, k), 4)
End If
If Mid(.Cells(i, k), 4, 2) = "12 " Then
.Cells(i, j) = Left(.Cells(i, k), 2) & " DEZ " & Right(.Cells(i, k), 4)
End If
' .............Der folgende Eintrag ändert die Tage 01 bis 09 nach 1 bis 9 .........
If Left(Range("BT" & i), 1) = "0" Then Range("BT" & i) = Mid(Range("BT" & i), 2, 20)
If Left(.Cells(i, k), 4) = "vor " Then
.Cells(i, j) = "BEF " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 5) = "nach " Then
.Cells(i, j) = "AFT " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 3) = "um " Then
.Cells(i, j) = "ABT " & Right(.Cells(i, k), 4)
End If
End With
End Sub
Mit
Select Case sieht das so aus
Code:
Sub Datum2()
Dim i As Long, j As Long, k As Long, Spalte1 As Range, Spalte2 As Range
'Geburts Datum
i = ActiveCell.Row
With Tabelle1
Set Spalte1 = .Rows(6).Find("Geburt-Datum")
If Not Spalte1 Is Nothing Then k = Spalte1.Column
Set Spalte2 = .Rows(6).Find("Geburt.-.Datum")
If Not Spalte2 Is Nothing Then j = Spalte2.Column
If Len(.Cells(i, k)) = 4 Then .Cells(i, j) = .Cells(i, k)
If Len(.Cells(i, k)) = 7 Then
Select Case Left(.Cells(i, k), 2)
Case "01"
.Cells(i, j) = "JAN " & Right(.Cells(i, k), 4)
Case "02"
.Cells(i, j) = "FEB " & Right(.Cells(i, k), 4)
Case "03"
.Cells(i, j) = "MRZ " & Right(.Cells(i, k), 4)
Case "04"
.Cells(i, j) = "APR " & Right(.Cells(i, k), 4)
Case "05"
.Cells(i, j) = "MAI " & Right(.Cells(i, k), 4)
Case "06"
.Cells(i, j) = "JUN " & Right(.Cells(i, k), 4)
Case "07"
.Cells(i, j) = "JUL " & Right(.Cells(i, k), 4)
Case "08"
.Cells(i, j) = "AUG " & Right(.Cells(i, k), 4)
Case "09"
.Cells(i, j) = "SEPT " & Right(.Cells(i, k), 4)
Case "10"
.Cells(i, j) = "OKT " & Right(.Cells(i, k), 4)
Case "11"
.Cells(i, j) = "NOV " & Right(.Cells(i, k), 4)
Case "12"
.Cells(i, j) = "DEZ " & Right(.Cells(i, k), 4)
End Select
Else
Select Case Mid(.Cells(i, k), 4, 2)
Case "01"
.Cells(i, j) = Left(.Cells(i, k), 2) & " JAN " & Right(.Cells(i, k), 4)
Case "02"
.Cells(i, j) = Left(.Cells(i, k), 2) & " FEB " & Right(.Cells(i, k), 4)
Case "03"
.Cells(i, j) = Left(.Cells(i, k), 2) & " MRZ " & Right(.Cells(i, k), 4)
Case "04"
.Cells(i, j) = Left(.Cells(i, k), 2) & " APR " & Right(.Cells(i, k), 4)
Case "05"
.Cells(i, j) = Left(.Cells(i, k), 2) & " MAI " & Right(.Cells(i, k), 4)
Case "06"
.Cells(i, j) = Left(.Cells(i, k), 2) & " JUN " & Right(.Cells(i, k), 4)
Case "07"
.Cells(i, j) = Left(.Cells(i, k), 2) & " JUL " & Right(.Cells(i, k), 4)
Case "08"
.Cells(i, j) = Left(.Cells(i, k), 2) & " AUG " & Right(.Cells(i, k), 4)
Case "09"
.Cells(i, j) = Left(.Cells(i, k), 2) & " SEPT " & Right(.Cells(i, k), 4)
Case "10"
.Cells(i, j) = Left(.Cells(i, k), 2) & " OKT " & Right(.Cells(i, k), 4)
Case "11"
.Cells(i, j) = Left(.Cells(i, k), 2) & " NOV " & Right(.Cells(i, k), 4)
Case "12"
.Cells(i, j) = Left(.Cells(i, k), 2) & " DEZ " & Right(.Cells(i, k), 4)
End Select
End If
' .............Der folgende Eintrag ändert die Tage 01 bis 09 nach 1 bis 9 .........
If Left(Range("BT" & i), 1) = "0" Then Range("BT" & i) = Mid(Range("BT" & i), 2, 20)
If Left(.Cells(i, k), 4) = "vor " Then
.Cells(i, j) = "BEF " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 5) = "nach " Then
.Cells(i, j) = "AFT " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 3) = "um " Then
.Cells(i, j) = "ABT " & Right(.Cells(i, k), 4)
End If
End With
End Sub
Vor allem wird hier nur einmal LEFT(xxx, 2) bzw. MID(xxx,4, 2) ausgeführt. Das beschleunigt die Durchführung um den Faktor 12. Durch Zuweisung der Monatsnamen zu einer Variablen und Eintragen in die Zelle nach dem
Select Case könnte man das noch etwas eleganter machen:
Code:
Sub Datum3()
Dim i As Long, j As Long, k As Long, Spalte1 As Range, Spalte2 As Range
Dim s As String
'Geburts Datum
i = ActiveCell.Row
With Tabelle1
Set Spalte1 = .Rows(6).Find("Geburt-Datum")
If Not Spalte1 Is Nothing Then k = Spalte1.Column
Set Spalte2 = .Rows(6).Find("Geburt.-.Datum")
If Not Spalte2 Is Nothing Then j = Spalte2.Column
If Len(.Cells(i, k)) = 4 Then .Cells(i, j) = .Cells(i, k)
If Len(.Cells(i, k)) = 7 Then
Select Case Left(.Cells(i, k), 2)
Case "01"
s = "JAN "
Case "02"
.Cells(i, j) = "FEB "
Case "03"
s = "MRZ "
Case "04"
s = "APR "
Case "05"
s = "MAI "
Case "06"
s = "JUN "
Case "07"
s = "JUL "
Case "08"
s = "AUG "
Case "09"
s = "SEPT "
Case "10"
s = "OKT "
Case "11"
s = "NOV "
Case "12"
s = "DEZ "
End Select
.Cells(i, j) = s & Right(.Cells(i, k), 4)
Else
Select Case Mid(.Cells(i, k), 4, 2)
Case "01"
s = " JAN "
Case "02"
s = " FEB "
Case "03"
s = " MRZ "
Case "04"
s = " APR "
Case "05"
s = " MAI "
Case "06"
s = " JUN "
Case "07"
s = " JUL "
Case "08"
s = " AUG "
Case "09"
s = " SEPT "
Case "10"
s = " OKT "
Case "11"
s = " NOV "
Case "12"
s = " DEZ "
End Select
.Cells(i, j) = Left(.Cells(i, k), 2) & s & Right(.Cells(i, k), 4)
End If
' .............Der folgende Eintrag ändert die Tage 01 bis 09 nach 1 bis 9 .........
If Left(Range("BT" & i), 1) = "0" Then Range("BT" & i) = Mid(Range("BT" & i), 2, 20)
If Left(.Cells(i, k), 4) = "vor " Then
.Cells(i, j) = "BEF " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 5) = "nach " Then
.Cells(i, j) = "AFT " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 3) = "um " Then
.Cells(i, j) = "ABT " & Right(.Cells(i, k), 4)
End If
End With
End Sub
Es geht aber noch eleganter, komplett ohne
If oder
Select Case:
Code:
Sub Datum4()
Dim i As Long, j As Long, k As Long, Spalte1 As Range, Spalte2 As Range
'Geburts Datum
i = ActiveCell.Row
With Tabelle1
Set Spalte1 = .Rows(6).Find("Geburt-Datum")
If Not Spalte1 Is Nothing Then k = Spalte1.Column
Set Spalte2 = .Rows(6).Find("Geburt.-.Datum")
If Not Spalte2 Is Nothing Then j = Spalte2.Column
If Len(.Cells(i, k)) = 4 Then .Cells(i, j) = .Cells(i, k)
If Len(.Cells(i, k)) = 7 Then
.Cells(i, j) = UCase(Format("01." & Left(.Cells(i, k), 2) & ".00", "MMM ")) & Right(.Cells(i, k), 4)
Else
.Cells(i, j) = Left(.Cells(i, k), 2) & UCase(Format("01." & Mid(.Cells(i, k), 4, 2) & ".00", "MMM ")) & Right(.Cells(i, k), 4)
End If
' .............Der folgende Eintrag ändert die Tage 01 bis 09 nach 1 bis 9 .........
If Left(Range("BT" & i), 1) = "0" Then Range("BT" & i) = Mid(Range("BT" & i), 2, 20)
If Left(.Cells(i, k), 4) = "vor " Then
.Cells(i, j) = "BEF " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 5) = "nach " Then
.Cells(i, j) = "AFT " & Right(.Cells(i, k), 4)
End If
If Left(.Cells(i, k), 3) = "um " Then
.Cells(i, j) = "ABT " & Right(.Cells(i, k), 4)
End If
End With
End Sub
Wenn man jetzt noch weiß, dass das deutsche Excel die kurzen Monatsnamen in der siebten benutzerdefinierten Liste hinlegt hat (die englischen sind in Liste 3), dann kann man den länglichen Ausdruck in
UCase() noch etwas kürzen: aus
UCase(Format("01." & Left(.Cells(i, k), 2) & ".00", "MMM ")) wird dann
UCase(arr(--Left(.Cells(i, k), 2)) & " ") bzw. analog dann mit Mid() statt Left().
Und mal so ganz nebenbei bemerkt: ist es nicht etwas inkonsequent, wenn man für die Monatsnamen deutsche Abkürzungen verwendet, aber für die Zeitbereiche englische?