AW: Inhalte versch. Zellen als Aufzählung in eine Zell
18.12.2007 10:46:00
HermannZ
Hallo Katrin;
wenn du folgende Funktion verwendest kannst du das einfach bewerkstelligen;
In ein Modul kopieren
Function VERKETTENTEXT(Trennzeichen As String, ParamArray Text()) As String
Dim TString As String
Dim i, ii, z, LB As Long
Dim Zelle As Range
Dim IsArrayAlloc As Boolean
If UBound(Text) - LBound(Text) + 1 = 0 Then
VERKETTENTEXT = vbNullString
Exit Function
End If
For i = LBound(Text) To UBound(Text)
If IsObject(Text(i)) = True Then
If TypeOf Text(i) Is Excel.Range Then
For Each Zelle In Text(i).Cells
TString = TString & Zelle.Text & Trennzeichen
Next Zelle
Else
VERKETTENTEXT = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Text(i)) = True Then
On Error Resume Next
IsArrayAlloc = (Not IsError(LBound(Text(i))) And (LBound(Text(i)) 0
LB = LBound(Text(i), z)
If Err.Number = 0 Then
z = z + 1
Else
z = z - 1
End If
Loop
If z > 2 Then
VERKETTENTEXT = CVErr(xlErrValue)
Exit Function
End If
If z = 1 Then
For ii = LBound(Text(i)) To UBound(Text(i))
If Text(i)(ii) vbNullString Then
TString = TString & Text(i)(ii) & Trennzeichen
End If
Next ii
Else
For ii = LBound(Text(i), 1) To UBound(Text(i), 1)
If Text(i)(ii, 1) vbNullString Then
TString = TString & Text(i)(ii, 1) & Trennzeichen
End If
Next ii
For ii = LBound(Text(i), 2) To UBound(Text(i), 2)
If Text(i)(ii, 2) vbNullString Then
TString = TString & Text(i)(ii, 2) & Trennzeichen
End If
Next ii
End If
Else
TString = TString & Text(i) & Trennzeichen
End If
Else
TString = TString & Text(i) & Trennzeichen
End If
Next i
If Len(Trennzeichen) > 0 Then
TString = Left(TString, Len(TString) - Len(Trennzeichen))
End If
VERKETTENTEXT = TString
End Function
Der Aufruf in der Tabelle;
=VERKETTENTEXT(gewünschtes Trennzeichen;Bereich)
für dein Beispiel;
=VERKETTENTEXT(" ";A1:A512)
Gruss HermannZ