Microsoft Excel

Herbers Excel/VBA-Archiv

right(instrrev) bis erstes "-" von rechts

Betrifft: right(instrrev) bis erstes "-" von rechts von: Bo Klein
Geschrieben am: 28.01.2020 09:45:36

Hallo,
ich möchte aus der letzten Zelle einer Liste mit Inhalt der Form 0000-00-##0 den Bereich ##0, also bis zum ersten - von rechts auslesen. Die Länge des letzten Strings variiert leider, sodass ich nicht einfach right(string,3 ) anwenden könnte. Aktuell funktionieren beide Varianten zur Ermittlung der letzten Zeile nicht.

With ThisWorkbook.Worksheets("E")
'lastE = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
lastE = .Cells(Rows.Count, 2).End(xlUp).Row 'letzte Zeile finden
lastRef = Right(.Cells(lastE, 2).Value, InStrRev(.Cells(lastE, 2).Value, "-")) 'letzte Ref finden (fortlaufende Nummer)
Me.Text_Ref.Value = Year(Now) & "-" & Month(Now) & "-" & "lastRef +1 " 'neue Referenz mit fortlaufender Nummer+1 erstellen
End With

Vielen Dank im Voraus

Betrifft: AW: ist der "-" immer vorhanden?
von: JoWE
Geschrieben am: 28.01.2020 10:05:30



Betrifft: AW: ist der "-" immer vorhanden?
von: Bo Klein
Geschrieben am: 28.01.2020 10:18:43

Hallo,

ja, der Zellwert könnte z.B. 2020-01-032 lauten. Aktuell wird mir irgendwie trotz der Formatierung:
.Cells(last, 2).NumberFormatLocal = "0000-00-000" ab und zu z.b. 2020-1-32 erzeugt, was ich eigentlich nicht möchte.

Anscheinend passen die Typen in ... nicht, für InStrRev bekomme ich manchmal null und manchmal 8:
lastRef = Right(.Cells(lastE, 2).Value, InStrRev(.Cells(lastE, 2).Value, "-"))

Betrifft: AW: ist der "-" immer vorhanden?
von: JoWE
Geschrieben am: 28.01.2020 10:19:00

vllt. so?
Me.Text = Right(Cells(lastE, 2), Len(Cells(lastE, 2)) - InStrRev(Cells(lastE, 2), "-"))

Hinzufügen von Jahr und Monat und der fortlaufenden Nummer kriegst Du selbst hin?!
Jochen

Betrifft: Oder so...
von: Torsten
Geschrieben am: 28.01.2020 10:22:58


Me.Text = Mid(.Cells(lastE, 2), InStrRev(.Cells(lastE, 2), "-") + 1)

Gruss Torsten

Betrifft: AW: ist der "-" immer vorhanden?
von: Bo Klein
Geschrieben am: 28.01.2020 10:25:41

Der Code würde dann so aussehen:

With ThisWorkbook.Worksheets("E")
lastE = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
lastRef = Right(Cells(lastE, 2), Len(Cells(lastE, 2)) - InStrRev(Cells(lastE, 2), "-"))
Me.Text_Ref.Value = Year(Now) & "-" & Month(Now) & "-" & lastRef + 1 'neue Referenz mit fortlaufender Nummer+1 erstellen
End With


Bekomme leider immer noch den Laufzeitfehler in Zeile lastRef. lastE ist als Integer und lastRef als Long definiert.

Danke für die schnelle Rückmeldung

Betrifft: AW: ist der "-" immer vorhanden?
von: Torsten
Geschrieben am: 28.01.2020 10:28:11

lastRef muss String sein. Hier bekommst du ja dein Ergebnis aus dem String. Wenn du das Ergebnis als Zahl brauchst, musst du das nachher noch umwandeln mit CDbl.

Gruss Torsten

Betrifft: AW: ist der "-" immer vorhanden?
von: JoWE
Geschrieben am: 28.01.2020 10:32:57

Me.Text_Ref = Year(Now) & "-" & Month(Now) & "-" & lastRef + "1"

Betrifft: AW: right(instrrev) bis erstes "-" von rechts
von: Daniel
Geschrieben am: 28.01.2020 11:10:15

HI
auch wenn InstrRef das Zeichen von rechts sucht, das Ergebnis ist die Position des Zeichen im Text von links.
daher ist InstrRef mit MID zu kombinieren und nicht mit Right.
das Ergebnis ist ein Text. Wenn du diesen um 1 erhöhen willst, solltest du ihn hierfür in eine Zahl wandeln, um sicher zu gehen, dass VBA das "+" ausch als Additionszeichen verwendet und nicht als Verkettungsymbol.
astRef = Mid(.Cells(lastE, 2).Value, InStrRev(.Cells(lastE, 2).Value, "-") + 1)
Me.Text_Ref.Value = Year(Now) & "-" & Month(Now) & "-" & CLng(lastRef) + 1  
Gruß Daniel

Betrifft: Liest du auch manchmal die Antworten von anderen..
von: Torsten
Geschrieben am: 28.01.2020 11:15:14

..das ist exakt auch meine Loesung gewesen.

Betrifft: sorry, hatte ich übersehen
von: Daniel
Geschrieben am: 28.01.2020 11:22:45

in den folgeantworten war wieder nur RIGHT zu lesen.

Betrifft: AW: sorry, hatte ich übersehen
von: Bo Klein
Geschrieben am: 28.01.2020 11:37:30

Vielen Dank Jochen und Thorsten. Das funktioniert soweit, um es in meine UserForm richtig zu übernehmen und die neue Referenz zu generieren. Leider wird die Referenz nach wie vor falsch formatiert in der Zelle angezeigt. Jetzt nicht nur ohne die zusätzlichen Nullen, sondern als 43861 bzw. mit der Formatierung des Zellbereichs als 0000-43-861. Für mich sieht das nach einem falsch formatierten Datum aus, was auch nachvollziehbar ist, da ja immerhin Year und Month genutzt werden. Der Zellbereich wird mit Übernahme in die Zellen aus der UserForm mit .Cells(last, 2).NumberFormatLocal = "0000-00-000" vor und nach dem Kopiervorgang formatiert. Gibt es eine Möglichkeit, dass das auch in dem einheitlichen Format (zb.: 2020-01-030) übernommen wird?

Betrifft: AW: sorry, hatte ich übersehen
von: Daniel
Geschrieben am: 28.01.2020 11:55:48

Hi
Excel wird immer versuchen, so einen Text in ein Datum zu wandeln, insbesondere dann, wenn du ihn in eine Zelle schreibst.
du solltest beim Schreiben so eines Wertes immer das Hochkomma ' voranstellen, dann weiß Excel das dies ein Text ist, der nicht in eine Zahl, Datum oder Formel gewandelt werden darf, auch wenn dieser so aussieht.

oder du verwendest überall den Unterstrich als Trennzeichen: 2020_01_030.
dieser ist in Excel kein reguläres Trennzeichen für ein Datum, daher bleibt so was immer Text.

Gruß Daniel

Betrifft: AW: sorry, hatte ich übersehen
von: Bo Klein
Geschrieben am: 28.01.2020 12:50:32

ok, super Daniel, vielen Dank! Das ist simpel, wusste ich aber nicht. Gibt es dann eine ähnlich einfache Lösung, dass alle Nullen angezeigt werden? Also nicht 2020_1_30, sondern ein 2020_01_030 übernommen wird, da eigentlich bedeutet die Null im NumberFormatLocal, die Definition der Position im Gegensatz zur #.

Gruß bo

Betrifft: AW: sorry, hatte ich übersehen
von: Daniel
Geschrieben am: 28.01.2020 12:58:19

Hi

Wenn du den Wert im 1 erhöhst, hast du immer eine Zahl und Zahlen haben keine führenden Nullen.
Wenn du diese haben willst, musst du in VBA die Funktion Format(Wert, "000") verwenden.

Gruß Daniel

Betrifft: Split() statt InStrRev()
von: Rudi Maintaire
Geschrieben am: 28.01.2020 12:27:28

Hallo,
With ThisWorkbook.Worksheets("E")
  lastE = .Cells(Rows.Count, 2).End(xlUp).Row 'letzte Zeile finden
  tmp = Split(.Cells(lastE, 2).Value, "-")
  lastRef = tmp(Ubound(tmp)) * 1 'letzte Ref finden (fortlaufende Nummer)
  Me.Text_Ref.Value = Format(Date, "YYYY-MM-") & Format(lastRef + 1, "000") 'neue Referenz mit  _
fortlaufender Nummer+1 erstellen
End With

Gruß
Rudi

Beiträge aus dem Excel-Forum zum Thema "right(instrrev) bis erstes "-" von rechts"