[VBA] Daten an Liste anhängen

[Rabe]

Hallo,

ich habe eine intelligente Liste und ein paar Eingabefelder auf einem Blatt.

Nun wähle ich in einem Eingabefeld ein Neugerät aus, definiere in Zelle C7 die Serie, z.B. "B-Serie" und in Zelle E7-G7 (verbunden) ein Gerät mit der Bezeichnung z.B. "B18".

Die intelligente Liste im Bereich U1 bis AC21 hat als Spaltenüberschriften den Namen der Serie.

Teil 1:
Nun soll per VBA in der Liste das neue Gerät in die erste freie Zeile der betreffenden (richtigen) Spalte eingetragen werden.
In obigem Beispiel steht z.B. das "B-Serie" in der Spalte V und es gibt bisher 5 B-Geräte, also ist die erste freie die Zeile 7, das "B18" muß also in Zelle "V7" eingetragen werden.
Wie kann ich das mit VBA programmieren?

Teil 2:
Anschließend soll die Liste in allen Spalten sortiert werden von 1 bis Ende, aber in aufsteigender Sortierreihenfolge, daß also B1, B2, B3, B4, ... und dann erst B10, B11, B12, ... folgen.
Wie löse ich das über VBA?


  Daten in Liste einfügen.xlsb (Größe: 15,88 KB / Downloads: 11)

[schauan]

Hallo Ralf,

Teil1
ich nehme mal an, für den Eintrag ist der Knackpunkt, wie Du in die Tabelle kommst? Hier mal als Ansatz für einen fixen Eintrag in die B-Spalte. Statt der 15 nimmst Du Deinen Zellbezug. Für die Spaltenzahl könntest Du den Zeichencode verwenden. A-Serie beginnt mit A, A ist das Zeichen 65 , 1 = 65 - 64 ...

Code:

With ActiveSheet.ListObjects("Geräte445")
      .DataBodyRange.Cells(.DataBodyRange.Cells(.ListRows.Count + 1, 2).End(xlUp).Row, 2).Value = 15
     End With

Übrigens bringt das End(xlup) hier nicht die letzte belegte Zelle, sondern gleich die erste freie. ListRows.Count + 1 deshalb, falls die Tabelle bis unten gefüllt ist.

Teil2 folgt morgen

[snb]

Die Antwort: das ist zuviel Aufwand so lange du verbundene Zellen verwendest.
Verbundene Zellen und VBA ist eine unglückliche Kombination, und deswegen auch deine Frage 'wie löse ich das mit VBA'?
Wenn du verzichtest auf verbundene Zellen können wir weiter reden.... :05:

PS. Mit eine richtige 'database'Struktur und einem Userform ist deine Frage sehr einfach zu beantworten.

[Rabe]

Hi André,

Teil1
ich nehme mal an, für den Eintrag ist der Knackpunkt, wie Du in die Tabelle kommst? Hier mal als Ansatz für einen fixen Eintrag in die B-Spalte. Statt der 15 nimmst Du Deinen Zellbezug. Für die Spaltenzahl könntest Du den Zeichencode verwenden. A-Serie beginnt mit A, A ist das Zeichen 65 , 1 = 65 - 64 ...

Teil2 folgt morgen

jetzt habe ich natürlich das Problem, daß ich die Beispieltabelle zu sehr vereinfacht habe:
Die Namen der Serie heißen nicht A-Serie, B-Serie, usw., sondern
X-Serie, XB-Serie, XM-Serie, Xi-Serie, N-Serie, NE-Serie, P-Serie, PE-Serie, Sonstige

Die Geräte haben aber immer als Anfangsbuchstaben die Bezeichnungen, die in der Serie vor dem "-" stehen (oder eben beliebige andere Buchstaben für Sonstige)
Wie geht das dann?

@snb:
über eine Userform möchte ich die Eingabe nicht machen.
Die Daten werden aus der Eingabe-Tabelle mittels Makro in eine Datenbank-Tabelle "Datenbank" mit korrekter Daten-Struktur geschrieben. Deswegen stören bei anschließenden Aktionen die verbundenen Zellen in der Eingabemaske nicht.
Von der Datenbank könnte dann ja das neue Gerät in die Liste "Geräte445" eingetragen werden. Die Geräte stehen in der Datenbank in Spalte C und die Zeile des neuen Gerätes ist dann immer "loZeile".

[schauan]

Hallo Ralf,

das ist auch kein Problem. Ich hab hier mal zwei Ansätze, wie man vorgehen könnte. Die unterscheiden sich nur in der Art des Füllens der Collection. Die Collection hat gegenüber dem Array den Vorteil, dass Du über den Key die zugehörigen Werte direkt und ohne Schleife ausgelesen bekommst - siehe Code für die MsgBox.

Beim Füllen der Tabelle musst Du aber noch aufpassen, dass kein Wert doppelt kommt. Minimalvariante wäre da wohl eine Datengültigkeit, die anhand der Inhalte der Tabelle Duplikate nicht zulässt. Ggf. kann man trotzdem noch eine Sicherheit im Code einbauen,

Code:

ub test1()
'Variablendeklaration
'String
Dim sSerie$
'Collection fuer Kuerzel
Dim colA As Collection
'Feldname festlegen
sSerie = "XB-Serie"
'Collection eröffnen
Set colA = New Collection
'Elemente der Collection hinzufuegen, Wert und Key
colA.Add 1, "X"
colA.Add 2, "XB"
colA.Add 3, "XM"
'Wert anhand Key aus Feldname auslesen und ausgeben
MsgBox colA(IIf(InStr(1, sSerie, "-") > 0, Split(sSerie, "-")(0), "S"))
End Sub

Sub test2()
Dim sSerie$
Dim colA As Collection, arrTYpen, iCnt%
sSerie = "XB-Serie"
Set colA = New Collection
'Array mit Keys bilden
arrTYpen = Array("X", "XB", "XM")
'Schleife ueber alle Keys
For iCnt = 0 To UBound(arrTYpen)
  'Elemente der Collection hinzufuegen, Wert und Key
  colA.Add iCnt + 1, arrTYpen(iCnt)
'Ende Schleife ueber alle Keys
Next
'Wert anhand Key aus Feldname auslesen und ausgeben
MsgBox colA(IIf(InStr(1, sSerie, "-") > 0, Split(sSerie, "-")(0), "S"))
End Sub

[Rabe]

Hi Andrè,

das ist auch kein Problem. Ich hab hier mal zwei Ansätze, wie man vorgehen könnte. Die unterscheiden sich nur in der Art des Füllens der Collection. Die Collection hat gegenüber dem Array den Vorteil, dass Du über den Key die zugehörigen Werte direkt und ohne Schleife ausgelesen bekommst - siehe Code für die MsgBox.

Beim Füllen der Tabelle musst Du aber noch aufpassen, dass kein Wert doppelt kommt. Minimalvariante wäre da wohl eine Datengültigkeit, die anhand der Inhalte der Tabelle Duplikate nicht zulässt. Ggf. kann man trotzdem noch eine Sicherheit im Code einbauen,

Danke, habe es nun mit beiden Codes hinbekommen.
Eine Doppelung gibt es eigentlich nicht, da das nur bei einem Neugerät, das noch nicht in der Tabelle ist, aufgerufen wird.

Nun habe ich nur noch das Problem, daß das Gerät in der letzten = einer neuen Zeile des gesamten benannten Bereiches eingefügt wird, auch wenn in dieser speziellen Spalte gar nicht so viele ausgefüllt sind. Es müßte noch ein weiteres End(xlUp) gemacht werden, um auf den letzten Eintrag im benannten Bereich in der betreffenden Spalte zu kommen.

Dies ist mein Code:

Sub Eintrag()

  Call Spalte_in_Serie

  

  With ActiveSheet.ListObjects("Geräte")

     .DataBodyRange.Cells(.DataBodyRange.Cells(.ListRows.Count + 1, lngQ).End(xlUp).Row, lngQ).Value = Range("E7")

  End With

  ''Dies funktioniert genau so wie oben, egal ob mit loLetzte oder mit loZeile: 

  'With ThisWorkbook.Worksheets("Eingabe_ELC") 

  '   loLetzte = .Cells(Rows.Count, lngQ + 22).End(xlUp).Row         ' letzte belegte in Spalte der Serie 

  '   loZeile = loLetzte + 1 

  '   Cells(loLetzte, lngQ + 22) = Range("E7") 

  'End With 

  

End Sub



Sub Spalte_in_Serie()

  'Variablendeklaration 

  Dim sSerie$                'String 

  Dim colA As Collection     'Collection fuer Kuerzel 

  

  sSerie = Range("C7")       'Feldname festlegen 

  Set colA = New Collection  'Collection eröffnen 

  

  'Elemente der Collection hinzufuegen, Wert und Key 

  colA.Add 1, "X"

  colA.Add 2, "XB"

  colA.Add 3, "XM"

  colA.Add 4, "Xi"

  colA.Add 5, "N"

  colA.Add 6, "NE"

  colA.Add 7, "P"

  colA.Add 8, "PE"

  colA.Add 9, "S"

  'Wert anhand Key aus Feldname auslesen und ausgeben 

  lngQ = colA(IIf(InStr(1, sSerie, "-") > 0, Split(sSerie, "-")(0), "S"))

  'MsgBox lngQ 

End Sub

Teil 2:
jetzt können wir uns an das logische Sortieren der Geräte pro Spalte machen.

[Rabe]

Hi,

Dies ist mein Code:

wie kann ich nun in der Datengültigkeit den Bereich in der Formel auf die letzte Zeile des benannten Bereichs setzen? Also am einfachsten statt $W$2:$AE$21 den Namen des benannten Bereichs "Geräte" verwenden?

Eingabe_ELC

	Q	R	S
7

verbundene Zellen

Q7:S7

Daten, Gültigkeit
Zelle	Zulassen	Daten	Wert1	Wert2	Leere Zellen ignorieren	Zellendropdown	Titel Eingabemeldung	Eingabemeldung	Titel Fehler	Fehlermeldung	Typ Fehlermeldung
Q7:S7	Liste		=INDEX($W$2:$AE$21;;VERGLEICH($C$7;$W$1:$AE$1;))		Wahr	Wahr					Stopp

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit