Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1512to1516
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Excel Makro: Maxwert finden, plus 1 und einfügen

Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 13:17:17
Thomas
Hallo zusammen!
Dies ist mein erster Beitrag als User, weshalb ich darum bitte, bei eventuellen Formfehlern meinerseits Gnade walten zu lassen...
In meiner Tabelle befinden sich Gegenstände (Spalte D) und jeweils eine Nr (Spalte B). In Spalte C bringe ich die "Nr" auf das von mir gewünschte Format. Die ersten Nummern habe ich per Hand eingetragen (1,2,...). Etwa wie folgt:
Zeile | Auswahl | Nummer | ID | Gegenstände
12 | | 1 | 00001 | Kaffeemaschine
13 | | 2 | 00002 | Kühlschrank
14 | | 3 | 00003 | Tisch
Nun möchte ich bei jedem neuen Eintrag in Spalte D automatisch die nächste Nummer in Spalte B _
einfügen. Bisher löse ich das mit folgendem Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Or _
Target.Count > 1 Then Exit Sub
Target.Offset(, -2) = IIf(Len(Target) > 0, Target.Row - 11, "")
End Sub

Nun möchte ich aber auch Artikel löschen können!! Da der Code nur die Zeilen zählt, wird nach löschen von z.B. Zeile 13 aber erneut die "3" vergeben, was ja das Ziel der eindeutigen Nr verfehlt.
Meine Idee geht, wie im Betreff bereits geschrieben, in Richtung eines Codes, welcher den Maximalwert einer Spalte findet, eine 1 addiert und in der ersten freien Zelle (ab Zeile 12) der Spalte A den berechneten Wert einsetzt.
Bei meiner Suche schon länger dauernden Suche im Netz habe ich diesen Code

Sub maxpluseins()
Sheets("Tabelle1").Range("C12").Value = Application.WorksheetFunction.Max(Sheets("Tabelle2"). _
Range("B4:B24")) + 1
End Sub

..gefunden.
Allerdings reichen meine VBA-Kenntnisse noch nicht, um es an ActiveSheet, ein variables Target (also Einfügen immer unterhalb der Tabelle) anzupassen (Entschuldigung, falls dieser Satz den Profis unter euch die Haare zu Berge stehen lässt). Außerdem wird dafür eine "Schaltfläche" benötigt, mir wäre aber eine automatische Reaktion wie im ersten Code wichtig, um Flüchtigkeitsfehler von Beginn an auszuschließen.
Meinen bisherigen Ansatz habe ich als Datei angehängt.
Ich bedanke mich schon einmal im voraus bei jedem, der seine Zeit auch nur mit dem Lesen meines Problems verbringt, ganz zu schweigen von denen, welche sich auch noch an die Lösung begeben!
Liebe Grüße!
https://www.herber.de/bbs/user/108313.xlsm

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 13:26:56
yummi
Hallo Thomas,
dafür brauchst Du nicht zwingend VBA.
Schreib doch einfach in die Spalte A erste Zeile deien Liste eine 1 dann in der nachsten Zeile Spalte A z.B.Zeile 2 =Wenn($D2=""; ""; &A1+1)
Diese Formel ziehst Du dann einfach runter so weit du willst. Ich denke das liefert dir das was Du brauchst
gruß
yummi
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 13:35:05
Thomas
Hallo Yummi,
erschreckend wie schnell hier die Antworten kommen... Daran hatte ich auch schon gedacht, und natürlich nicht geschrieben, dass ich die Tabelle sortieren können muss.
Wenn ich dies jedoch mit deiner Methode tue, kommen die Bezüge durcheinander. Das Sortieren ist der Grund weshalb ich überhaupt per VBA einen WERT einfügen, und eben keine Formel nehmen möchte. Hätte man natürlich auch eingangs erwähnen können... Sry
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 13:40:47
Thomas
habe natürlich den "Noch-Offen"-Haken vergessen zu setzen... sry
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 13:46:29
yummi
Hallo Thomas,
lad mal deine Liste hoch und bau die Steuerung schon mal ein (ohne Funktion)
mit Beschreibung was genau passieren soll.
Dann ist es einfacher zu helfen
Gruß
yummi
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 14:08:00
Thomas
Hallo Yummi,
am Ende der Nachricht erscheint hoffentlich der Link zur Datei... Unter File-Upload steht, man müsse den Link nur unterhalb des Beitrags einfügen. Hatte ich auch beim Eröffnungsbeitrag probiert. Hoffe es klappt jetzt...
Falls nicht hier meine Anforderungen noch einmal auf diesem Wege:
1.Tabelle soll bei Sortieren alle Werte in der jeweiligen Zeile erhalten. (was ich mit der von dir erwähnten Formel leider nicht hinbekomme)
2.Bei neuem Eintrag eines Gegenstands soll automatisch die nächste Nr in Spalte B eingefügt werden. Bisher erledigt durch das "Count.Rows"-VBA
3. Betreffende Zeilen sollen nach Abgang des Gegenstands gelöscht werden können. Da das jedoch die Zeilenanzahl der Tabelle verändert, bekomme ich im Augenblick (mit Count.Rows) nach Löschen von Zeile 12 erneut die Nr "3" vergeben, statt richtigerweise "4". Deshalb meine Überlegung den MaxWert zu suchen und ihn mit 1 addiert einzufügen.
Ich hoffe du wirst daraus schlau :)
Danke im voraus und Grüße.
Und hier der Link...

Die Datei https://www.herber.de/bbs/user/108314.xlsm wurde aus Datenschutzgründen gelöscht


Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 14:21:26
Thomas
Und wieder dieses Kontrollkästchen...
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 14:22:44
yummi
Hallo Thomas,
änder dein makro mal wie folgt:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lmaxwert As Long
If Intersect(Target, Range("D:D")) Is Nothing Or _
Target.Count > 1 Then Exit Sub
lmaxwert = Application.Max(Range(Cells(11, "B"), Cells(Cells(Rows.Count, 2).End(xlUp).Row, " _
B")))
'Target.Offset(, -2) = IIf(Len(Target) > 0, Target.Row - 11, "")
Target.Offset(, -2) = lmaxwert + 1
End Sub
Gruß
yummi
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 14:43:13
Thomas
Hallo Yummi,
ich bekomme leider folgende Fehlermeldung:
Laufzeitfehler '13':
Typen unverträglich
Dachte zuerst, es liege an den Zellformaten. Ich konnte aber keine Verbesserung erzielen. Zudem habe ich das ' in der drittletzten Zeile entfernt, da Visual Basic es mir grün markiert hat (was doch nur Anmerkungen kennzeichnet und alles dahinter außer Funktion setzt?!). Oder hab ich jetzt den Bock geschossen?
Grüße,
Thomas
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 14:47:03
yummi
Hallo Thomas,
ich habe die Zeile bewusst auskommentiert, da sie dafür verantwortlich ist, dass du die Anzahl der Zeilen als Berechnungsgrundlage verwendest und nicht den bisherigen Maxwert. Ich habe das nur dringelassen, damit Du siehst was ich geändert habe.
Setz mal einen Breakpoint am Anfang deienr Funktion und step mal durch, wo genau bekommst Du denn den Laufzeitfehler?
Bei dem was Du hochgeladen hast geht es nämlich.
Gruß
yummi
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:01:49
Thomas
Hallo Yummi,
sobald ich einen neuen Gegenstand eintrage, bekomme ich besagten Laufzeitfehler. Habe zur Sicherheit auch noch einmal selbst die Datei heruntergeladen, bekomme aber den gleichen Fehler wie meiner Ursprungsdatei.
Visual Basic markiert mir in beiden Dateien folgende Zeile gelb:
lmaxwert = Application.Max(Range(Cells(11, "B"), Cells(Cells(Rows.Count, 2).End(xlUp).Row, " _B")))
Grüße und noch einmal Danke für die Mühe!
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:16:08
Thomas
Bitte verzeih mir mein gefährliches Halbwissen:
Muss ich eventuell noch irgendeine Library für diesen Befehl einbinden oder den Code an anderer Stelle als den ursprünglichen ablegen?
Macht mich doch stutzig, dass es bei dir funktioniert. Habe auch noch einmal kontrolliert ob ich falsch eingefügt habe. Es läuft aber immer wieder auf besagten Fehler hinaus...
Gruß,
Thomas
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:24:22
yummi
hallo thomas,
ich habe nichts besonderes eingebunden:
Visual basic for Application
Microsoft Excel 14.0 Object Libary
OLE Automation
Microsoft Office 14.0 Object Libary
Der Code gehört so wie vorher auch zum Arbeitsblatt
https://www.herber.de/bbs/user/108317.xlsm
Gruß
yummi
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:32:30
Werner
Hallo Thomas,
versuch mal:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Or Target.Count > 1 Then Exit Sub
Target.Offset(, -2).FormulaLocal = "=ZEILE()-ZEILE(B$11)"
End Sub
Gruß Werner
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:47:52
Thomas
Hallo Werner,
erst einmal Danke für deine Hilfe.
Dein Code fügt zwar die nächste Nr ein, setzt diese jedoch um einen zurück, sobald ich eine Zeile der Tabelle lösche. Auch bleiben die Werte nicht erhalten, wenn die Tabelle sortiert wird, da ja anscheinend eine Formel eingefügt wird, welche sich auf die aktuelle Zeile bezieht.
Deshalb der Gedanke einen Wert statt einer Formel einzufügen. Hättest du in der Richtung auch noch eine Idee?
Grüße und vielen Dank,
Thomas
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:56:55
Werner
Hallo Thomas,
jetzt nochmal nachgefragt. Wenn du die Zeile z.B. mit der vergebenen Nummer 3 löschst, dann sollen die folgenden Nummern nicht angepasst werden? Sprich du hast dann folgende Nummerierung: 1-2-4-5
Gruß Werner
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 16:01:30
Werner
Hallo Thomas,
wenn ja, dann so:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target.Offset(, -2)
.FormulaLocal = "=ZEILE()-ZEILE(B$11)"
.Value = .Value
End With
End Sub
Gruß Werner
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 19:24:37
Thomas
Hallo Werner,
dein Code trägt jetzt zwar einen Wert ein, allerdings nur den der Zeilenanzahl. Ich benötige allerdings nicht die Zeilenanzahl sondern den MaxWert +1.
Kleines Beispiel:
Ich trage den vierten Gegenstand ein. Er bekommt die korrekte Nr "4" zugewiesen. Lösche ich nun die dritte Tabellenzeile, so behält zwar der Gegenstand die Nr "4", aber ein neuer Eintrag wird so wiederum mit "4" versehen (was ja die richtige Zeilenanzahl ist). Es sollte aber "5" erscheinen, also MaxWert Spalte B +1 .
Trotzdem noch einmal vielen Dank für die Hilfe, bist ja anscheinend auch eher ausversehen hier gelandet:)
Schönen Abend dir noch und Grüße,
Thomas
Nach meinen Anforderungen sollte dieser also
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:18:45
yummi
Hallo Thomas,
also ich mache folgendes: Trage in Spalte D irgendetwas ein und drücke dann auf TAB.
Bedienst Du deine Liste irgendwie anders?
Bin jetzt erstmal offline
Gruß
yummi
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:30:36
Werner
Hallo,
versuch mal das:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Or Target.Count > 1 Then Exit Sub
Target.Offset(, -2).FormulaLocal = "=ZEILE()-ZEILE(B$11)"
End Sub
Gruß Werner
AW: Sorry, im falschen Ast gepostet. o.w.T.
21.09.2016 15:31:43
Werner
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 15:39:42
Thomas
Hallo Yummi,
ich habe ENTER benutzt. Allerdings bekomme ich auch mit TAB den selben Fehler gemeldet und die gleiche Zeile ...
lmaxwert = Application.Max(Range(Cells(11, "B"), Cells(Cells(Rows.Count, 2).End(xlUp).Row, " _B")))
... gelb markiert.
Wenn ich z.B. "Schrank" in Spalte D eintrage und TAB drücke, fügt er zwar "Schrank" ein und erweitert auch die Tabelle, fügt aber eben keine Nr ein und liefert den Laufzeitfehler.
Auch in der von dir hochgeladenen Datei bekomme ich selbiges Ergebnis...
Werde weiter probieren, schon einmal Danke für deine Hilfe bis hier hin!
Grüße,
Thomas
Anzeige
AW: Excel Makro: Maxwert finden, plus 1 und einfügen
21.09.2016 20:20:45
Thomas
Hallo Yummi,
habe jetzt mehrere Stunden deinen Ansatz durchsucht, mehrmals eine neue Datei angelegt und den Code reinkopiert, tausendmal die Verweise gecheckt und, und, und...
Habe dann aus lauter Verzweiflung deine Datei erneut runtergeladen und plötzlich (vor ner halben Stunde) funktioniert es!!!!! Das komische ist, dass es plötzlich auch in ALLEN zuvor benutzten/angelegten Dateien funzt, in denen vorher der Laufzeitfehler auftrat. PC wurde zwischendurch nicht runtergefahren oder sonst was... bin wirklich völlig ratlos woran es lag. Hauptsache es tritt morgen nicht wieder auf :)
Bin auf jeden Fall überglücklich, es macht genau das was und wie ich mir es gewünscht habe (übrigens auch mit ENTER)! Der Knaller!! Vielen, vielen Dank an dich (und natürlich auch an Werner)!!! Und dann auch noch so schnell... bin wirklich sehr begeistert!
Wünsche dir noch einen wunderbaren Restabend! Kann mich gar nicht genug bedanken deshalb noch einmal: Dank euch!
Mit leicht euphorischen Grüßen,
Thomas
Anzeige

315 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige