Option Explicit
Sub Claudia2()
Dim X&, y&, col&, loletzte&, Frage$
Frage$ = InputBox("Bitte Spaltenbuchstabe für Kombination wählen", "Kombination wählen", "G")
If StrPtr(Frage) = 0 Then Exit Sub 'Abbruch angeklickt
Select Case UCase(Frage) 'UCase - Vergleich in Großschrift
Case Is = "B"
col = 2
Case Is = "C"
col = 3
Case Is = "D"
col = 4
Case Is = "E"
col = 5
Case Is = "F"
col = 6
Case Is = "G"
col = 7
Case Is = "H"
col = 8
Case Is = "I"
col = 9
Case Is = "J"
col = 10
Case Is = "K"
col = 11
Case Is = "L"
col = 12
Case Is = "M"
col = 13
Case Else
Exit Sub
End Select
For X = 2 To 32
loletzte = Cells(Rows.Count, 1).End(xlUp).Row + 1
If Cells(X, 1) "" And Cells(X, col) "" Then
Cells(loletzte, 1) = Cells(X, 1) & Cells(X, col)
End If
Next
End Sub
Option Explicit
Public Frage$
Sub Claudia4()
Dim X&, col&, loletzte&
Frage$ = InputBox("Bitte Spaltenbuchstabe für Kombination wählen", "Kombination wählen", "G")
If StrPtr(Frage) = 0 Then Exit Sub 'Abbruch angeklickt
Select Case UCase(Frage) 'UCase - wandelt "klein geschriebene" Spaltenbuchstaben um in Groß _
schrift
Case Is = "B"
col = 2
Case Is = "C"
col = 3
Case Is = "D"
col = 4
Case Is = "E"
col = 5
Case Is = "F"
col = 6
Case Is = "G"
col = 7
Case Is = "H"
col = 8
Case Is = "I"
col = 9
Case Is = "J"
col = 10
Case Is = "K"
col = 11
Case Is = "L"
col = 12
Case Is = "M"
col = 13
Case Else
Exit Sub
End Select
'Wählen ob inkl. Spalte(A)
'oder nur den Wert der ausgewählten Spalte
If MsgBox("Kopiere Spalte: " & UCase(Frage) & vbLf & "inkl. KundenNr.: aus Spalte ""A"" ?", _
vbYesNo) = vbYes Then
For X = 2 To 32
loletzte = Cells(Rows.Count, 1).End(xlUp).Row + 1
If Cells(X, 1) "" And Cells(X, col) "" Then
Cells(loletzte, 1) = Cells(X, 1) & Cells(X, col) 'SpalteA + Auswahlspalte
End If
Next
Else
For X = 2 To 32
loletzte = Cells(Rows.Count, 1).End(xlUp).Row + 1
If Cells(X, 1) "" And Cells(X, col) "" Then
Cells(loletzte, 1) = Cells(X, col) 'nur Auswahlspalte
End If
Next
End If
End Sub
Option Explicit
Public Frage$
Sub Claudia6()
Dim X&, col&, loletzte&, LastCol&
Frage$ = InputBox("Bitte Spaltenbuchstabe(n) für Kombination wählen", "Kombination wählen", "G" _
)
If StrPtr(Frage) = 0 Then Exit Sub 'Abbruch angeklickt
If IsNumeric(Frage) Then Exit Sub 'Eingabe als Text (Spaltenbuchstaben) erzwingen
On Error GoTo Fehler
col = Cells(1, Frage).Column
'Zeile(der letzte Zelle) der ausgewählten Spalte wählen
LastCol = Cells(Rows.Count, col).End(xlUp).Row
'MsgBox "Spalte " & col & " wurde gewählt"
If MsgBox("Kopiere Spalte: " & UCase(Frage) & vbLf & "inkl. KundenNr.: aus Spalte ""A"" ?", _
vbYesNo) = vbYes Then
For X = 2 To LastCol
loletzte = Cells(Rows.Count, 1).End(xlUp).Row + 1
If Cells(X, 1) "" And Cells(X, col) "" Then
Cells(loletzte, 1) = Cells(X, 1) & Cells(X, col) 'SpalteA + Auswahlspalte
End If
Next
Else
For X = 2 To LastCol
loletzte = Cells(Rows.Count, 1).End(xlUp).Row + 1
If Cells(X, 1) "" And Cells(X, col) "" Then
Cells(loletzte, 1) = Cells(X, col) 'nur Auswahlspalte
End If
Next
End If
Exit Sub
Fehler:
MsgBox "Bitte nur gültige Spaltenbuchstaben eingeben !" & vbLf & "[ " & UCase(Frage) & " ] _
ist als Spalte nicht vorhanden", vbInformation, " Fehler"
End Sub