Office: spalten aneinander hängen + combolist hilfe

Moin Moin.
Ich stehe da vor einem Problem bei dem ich einfach nicht weiter komme :evil:

Ich habe 2 Spalten in einer Tabelle und möchte diese beiden einfach aneinander hängen, also:

a,b,c und d,e,f, wird a,b,c,d,e,f (wobei a,..,f die Daten in den zeilen der beiden Spalten sind)

leider bekommen ich das irgendwie immer nur als: a,b,c,0,[...],0,c,d,e,f hin.
Da die Spalten eine variable und ungleiche Länge von [1..40] haben.
Ich denke das geht über SVERWEIS aber habs leider nicht hinbekommen

Desweiteren möchte ich diese dann entstehende Spalte bei einer ComboBox in meinem UserForm verwenden.

Wie kann ich da der ComboBox die Daten aus der Tabelle übergeben? bzw den Gewählten string aus der ComboBox übernehmen?

Liebe Grüße und Vielen Dank

 

=A1&B1

 

danke aber nö, leider nicht
das (=A1&B1) also die Verkettung knn ich schon...
ich möchte nicht A1 und B1 in C1 zusammenfügen(verketten) sonden A1 soll in C1 und A2 soll in C2 usw. wenn A Fertig ist Sollen die Werte von B Folgen.
Also zB Letztes Element von A ist A9 dann:A1 in C1,..., A9 in C9,B1 in C10, B2 in C11,...

Maximale Zeilenanzahl von A=B=50.
Wenn ich das von Excel einfach so übernehmen lasse füllt er die freihen Zellen immer mit 0 auf.
Genau das will ich aber nicht da es für ein DropDown-Menü ziemlich lästig ist.
Die werte sollen Nahtlos aneinander gehängt werden.

 

Ich bin mir nicht sicher, ob man da nicht vielleicht was mit dynamischen Bereichen, WENN- und INDIREKT-Funktion was werden könnte...

Aber mein erster Ansatz wäre ein VBA-Makro. ;-)

Gruß, Michael

 

Hallo!

Als Formel-Vorschlag:

	A	B	C
1	a1	b1	a1
2	a2	b2	a2
3	a3	b3	a3
4	a4	b4	a4
5	a5	b5	a5
6		b6	b1
7		b7	b2
8			b3
9			b4
10			b5
11			b6
12			b7
13

Zelle	Formel
C1	=WENN(ZEILE()>ANZAHL2(A:A);INDEX(B:B;ZEILE()-ANZAHL2(A:A));INDEX(A:A;ZEILE()))

 

Hat wohl bloß zwei kleine Kinken:
a) Es wird mit "0" aufgefüllt bis zum Ende der Formel-Liste (schlecht für die Combo-Box)
b) Klappt nur sauber bei "lückenlosen" Listen in A:B (sonst gehen Werte verloren)

<TABLE border="1" cellspacing="0" cellpadding="3"><TR><TD width="20" bgcolor="#c0c0c0">.</TD><TD width="100" align="center" bgcolor="#c0c0c0">A</TD><TD width="100" align="center" bgcolor="#c0c0c0">B</TD><TD width="100" align="center" bgcolor="#c0c0c0">C</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">1</TD><TD bgcolor="#FFFFFF">a</TD><TD bgcolor="#FFFFFF">e</TD><TD bgcolor="#FFFFFF">a</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">2</TD><TD bgcolor="#FFFFFF">b</TD><TD bgcolor="#FFFFFF">f</TD><TD bgcolor="#FFFFFF">b</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">3</TD><TD bgcolor="#FFFFFF">c</TD><TD bgcolor="#FFFFFF">g</TD><TD bgcolor="#FFFFFF">c</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">4</TD><TD bgcolor="#FFFFFF">d</TD><TD bgcolor="#FFFFFF">h</TD><TD bgcolor="#FFFFFF">d</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">5</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">i</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">6</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">j</TD><TD bgcolor="#FFFFFF">e</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">7</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">k</TD><TD bgcolor="#FFFFFF">f</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">8</TD><TD bgcolor="#FFFFFF">z</TD><TD bgcolor="#FFFFFF">l</TD><TD bgcolor="#FFFFFF">g</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">9</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">h</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">10</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">i</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">11</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">j</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">12</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">k</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">13</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">l</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">14</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">15</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">16</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">17</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">18</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">19</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">20</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFF99">0</TD></TR></TABLE>

Gruß, Michael

 

Danke für die vielen Antworten;-)

@Michael: an ein solches Makro hab ich auch schon gedacht.
leider macht er -selbst wenn ich das makro selbst schreibe(mit for to schleifen usw)- das selbe wie bei dir. Die Spalte C hat nur so viele elemente wie die längste von A und B. Also in deinem Beispiel 8...schade.

Der ansatz von Schatzi hat ungefähr das selbe Resultat wie ich momentan bereits habe und verbessern will. Trotzdem danke :-D
Was das angeht hat miriki schon recht...

 

???

Also bei mir produziert es sauber:

<TABLE border="1" cellspacing="0" cellpadding="3"><TR><TD width="20" bgcolor="#c0c0c0">.</TD><TD width="100" align="center" bgcolor="#c0c0c0">A</TD><TD width="100" align="center" bgcolor="#c0c0c0">B</TD><TD width="100" align="center" bgcolor="#c0c0c0">C</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">1</TD><TD bgcolor="#FFFFFF">a</TD><TD bgcolor="#FFFFFF">e</TD><TD bgcolor="#FFFFFF">a</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">2</TD><TD bgcolor="#FFFFFF">b</TD><TD bgcolor="#FFFFFF">f</TD><TD bgcolor="#FFFFFF">b</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">3</TD><TD bgcolor="#FFFFFF">c</TD><TD bgcolor="#FFFFFF">g</TD><TD bgcolor="#FFFFFF">c</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">4</TD><TD bgcolor="#FFFFFF">d</TD><TD bgcolor="#FFFFFF">h</TD><TD bgcolor="#FFFFFF">d</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">5</TD><TD bgcolor="#FFFFFF">i</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">i</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">6</TD><TD bgcolor="#FFFFFF">j</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">j</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">7</TD><TD bgcolor="#FFFFFF">k</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">k</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">8</TD><TD bgcolor="#FFFFFF">l</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">l</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">9</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">e</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">10</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">f</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">11</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">g</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">12</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">h</TD></TR></TABLE>

Gruß, Michael

 

Hah, ja, doch ein kleiner Fehler, ich hab beim Tippen ganz simpel ein "x" verschluckt! Die Zeile

Code:

yn = s1.Cells(65536, 1).End(xlUp).Row

sollte eigentlich

Code:

yn = s1.Cells(65536, x1).End(xlUp).Row

heißen! Es sollte dort die Schleifen-Variable x1 benutzt werden, nicht konstant die Spalte 1. *grummelnörgelquengel*

Sonst funktioniert es nicht, wenn die 2. Spalte länger als die 1. ist...

Gruß, Michael

 

aso!
jo ich schaus mir nochma an.
Hab mich grad selbst nochma drann versucht:

folgendes ist bei raus gekommen:
Option Explicit
Sub Sort()
Dim C, i, j, temp As Integer
Dim rng As Range

For Each rng In Sheets("Namen").Range("C2:C105")
rng.ClearContents
Next

C = Sheets("Namen").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To C
Sheets("Namen").Cells(i, 3) = Sheets("Namen").Cells(i, 1)
Next i
i = C + 1
C = Sheets("Namen").Cells(Rows.Count, 2).End(xlUp).Row - 1
j = 2
For i = i To (i + C)
Sheets("Namen").Cells(i, 3) = Sheets("Namen").Cells(j, 2)
j = j + 1
Next i

End Sub

funktioniert...

kann mir noch einer bei der combobox weiterhelfen?

 

???

Tut's da nicht auch ein einfaches

Code:

worksheets("namen").range("c2:c105").clearcontents

Oder was bringt es, in dem Bereich jede Zelle einzeln zu löschen?

In dieser und der nächsten Schleife werden pauschal alle Zellen in Spalte 3 kopiert, auch wenn sie leer sind. Das sieht in der ComboBox ziemlich blöd aus, wenn die Listen in den Spalten 1 und 2 nicht lückenlos sind. Deswegen hatte ich in meiner Routine noch eine Abfrage auf "Zelle nicht leer" mit eingebaut.

Ggf. könntest Du auch noch eine Sortierung nach dem Zusammenkopieren mit einbauen. Das Ergebnis wäre dann sowas in der Art:

<TABLE border="1" cellspacing="0" cellpadding="3"><TR><TD width="20" bgcolor="#c0c0c0">.</TD><TD width="100" align="center" bgcolor="#c0c0c0">A</TD><TD width="100" align="center" bgcolor="#c0c0c0">B</TD><TD width="100" align="center" bgcolor="#c0c0c0">C</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">1</TD><TD bgcolor="#FFFFFF">a</TD><TD bgcolor="#FFFFFF">d</TD><TD bgcolor="#FFFFFF">a</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">2</TD><TD bgcolor="#FFFFFF">b</TD><TD bgcolor="#FFFFFF">e</TD><TD bgcolor="#FFFFFF">b</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">3</TD><TD bgcolor="#FFFFFF">c</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">c</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">4</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">f</TD><TD bgcolor="#FFFFFF">d</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">5</TD><TD bgcolor="#FFFFFF">g</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">e</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">6</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">j</TD><TD bgcolor="#FFFFFF">f</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">7</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">g</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">8</TD><TD bgcolor="#FFFFFF">h</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">h</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">9</TD><TD bgcolor="#FFFFFF">i</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">i</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">10</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">k</TD><TD bgcolor="#FFFFFF">j</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">11</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">l</TD><TD bgcolor="#FFFFFF">k</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">12</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">l</TD></TR></TABLE>

Die ComboBox zu füllen ist simpel: Die Eigenschaft "ListFillRange" muß dazu nur gesetzt werden. Das hab ich im angehängten Beispiel dann auch noch schnell mit eingebaut.

Gruß, Michael

 

ahhh danke ;-)

Naja das mit dem löschen der Spalten hab ich aus nem anderen Projekt, das ich irgendwo in nem Forum gefunden habe, übernommen.
Ich arbeite erst seit 2 Tagen mit vba und muss die Syntax/Semantik erst noch lernen ;-)

Da die Werte die ich aus den beiden spalten aneinander hängen will durch eine UserForm nahtlos aufeinander folgend eingetragen werden habe ich da nicht so den wert drauf gelegt zu prüfen ob sie leer sind oder nicht.
Aber ich werde den Punkt trotzdem übernehmen;-)

Danke für die viele Hilfe :mrgreen:

Eine Frage hätte ich aber noch.
Und zwar möchte ich in Tabelle: "Bahnen", alle Zeilen der Spalten A,B,C,D vor Veränderung schützen lassen, welche niedriger sind der Wert welcher bei "momentan"
eingetragen wurde. Ist so etwas (automatisch) überhaupt möglich?

Hab euch zum besseren Verständnis mal die File hochgeladen...

Falls das hier nicht mehr rein passt sagt es bitte, dann mache ich dazu einen neuen Thread auf...
Lg Norman

 

Das ist auch wohl nur per VBA möglich. Dazu sind mehrere Schritte notwendig:
* Der Blattschutz muß aufgehoben werden, wie es unter "Extras - Schutz - Blattschutz aufheben..." passiert.
* Der gesamte Bereich muß "entsperrt" werden, wie es unter "Format - Zellen... - Schutz - [ ] Gesperrt" in der Oberfläche passiert.
* Dann muß der Wert unter "momentan" ausgelesen werden und, sofern es eine gültige Zahl ist, der Bereich von A1:Dx gesperrt werden.
* Und dann muß der Blattschutz wieder aktiviert werden.
Und diese Prozedur läuft sinnvollerweise immer dann, wenn sich der "momentan"-Wert geändert hat.

Der folgende Code müßte dann mal ins Worksheet-Modul von "Bahnen":

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim y As Long
    Dim s1 As Worksheet

    If (Target.Row = 2) And (Target.Column = 5) Then

        y = 0: On Error Resume Next: y = Target.Value: On Error GoTo 0
        If (y >= 2) And (y < 65536) Then
            Set s1 = ActiveSheet

            s1.Unprotect

            s1.Cells.Locked = False

            s1.Range("A1:D" & y - 1).Locked = True

            s1.Protect

        End If
    End If

End Sub

Gruß, Michael

 

GEILES TEIL!
:lol:
Nur eins noch:
Was bedeutet die Zeile: "y = 0: On Error Resume Next: y = Target.Value: On Error GoTo 0"??

Und ich kann leider die erste zeile dauerhaft nicht mehr ändern :-?
eigendlich komisch weil ja: "If (y >= 2) And (y < 65536) Then"

Edit: Die zeilen A1:D4 sind dauerhaft geschützt :-?

 

noch was:
Wofür steht Target.Row?? Hab schon gesucht wie nen blöder aber keine vernünftige erklärung gefunden.
ich frage wegen: "If (Target.Row = 2) And (Target.Column = 5) Then"
ich weis du sprichst damit die 2. zeile 5. spalte an, aber warum mit target und getrennt?