Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1536to1540
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1536to1540
1536to1540
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kann man mit Makro Zellformat ändern?

Kann man mit Makro Zellformat ändern?
31.01.2017 10:04:21
Jenny
Guten Morgen,
bitte helft mir. Ich habe untenstehendes Makro, welches wie ihr seht im Sub Spalte E Werte einfügt.
Gibt es eine Möglichkeit, bei den Spalten C, J und K zu unterscheiden ob der einzufügende Wert eine Zahl größer als oder eine Zahl kleiner gleich 0 ist?
Wäre super, wenn das Makro bei Zahlen größer 0 diese im Zellformat Datum TT.MM.JJJJ einfügt und bei Zahlen kleiner 0 als Zahl ohne Nachkommastellen.
Könnt ihr mir dabei behilflich sein, das Makro abzuändern?
Danke
Jenny
 Private Sub Worksheet_Change(ByVal Target As Range)
Dim TC As Long
Dim c As Range
Application.ScreenUpdating = False
If Target.Columns.Count > 1 Then Exit Sub
If Target.Column = 5 Or Target.Column = 7 Then TC = Target.Column Else Exit Sub
'If Target.Count = 1 And Target  "" Then
On Error GoTo ERREXIT
Application.EnableEvents = False
Select Case TC
Case 5: For Each c In Target
If c  "" Then Call SpalteE(c)
Next
Case 7: For Each c In Target
If c  "" Then
Call SpalteG(c)
Call SpalteE(c)
End If
Next
End Select
ERREXIT:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub SpalteG(ByVal Target As Range)
Dim r As Range, c As Range, z&, cc As Range, zf&
Dim gefunden As Boolean
If Target.Offset(, -6)  "" Then
z = Target.Row
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Target.Offset(, -6).Value, _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 6) = Target Then
Target.Offset(, -2) = cc.Offset(, 4) '& " " & (cc.Offset(, 4).Address)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Target.Offset(, -2).Value = "n.v."
End If
End Sub
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
Cells(lngR, 6).FormulaR1C1 = Cells(1, 6).FormulaR1C1
Cells(lngR, 8).FormulaR1C1 = Cells(1, 8).FormulaR1C1
Cells(lngR, 9).FormulaR1C1 = Cells(1, 9).FormulaR1C1
Cells(lngR, 10).FormulaR1C1 = Cells(1, 10).FormulaR1C1
Cells(lngR, 11).FormulaR1C1 = Cells(1, 11).FormulaR1C1
Cells(lngR, 12).FormulaR1C1 = Cells(1, 12).FormulaR1C1
Rows(lngR).Copy
Cells(lngR, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Target.Select
End Sub

46
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kann man mit Makro Zellformat ändern?
31.01.2017 11:17:31
Werner
Hallo Jenny,
Makro in ein allgemeines Modul.
Am Ende deines "Kopiermakros" dann das Formatieren-Makro mit 
Call Spalten_formatieren

aufrufen
Sub Spalten_formatieren()
Dim loLetzte As Long
Dim loSpalte As Long
Dim rngBereich As Range
Dim rngZelle As Range
With Sheets("Tabelle1") 'Tabelle anpassen
For loSpalte = 3 To 17 Step 7
If loSpalte = 17 Then loSpalte = 11
loLetzte = .Cells(.Rows.Count, loSpalte).End(xlUp).Row
'## Beginn der Daten in Zeile 1 (.Cells(1, loSpalte) anpassen
Set rngBereich = .Range(.Cells(1, loSpalte), .Cells(loLetzte, loSpalte))
For Each rngZelle In rngBereich
If rngZelle.Value > 0 Then
rngZelle.NumberFormat = "DD.MM.YYYY"
ElseIf rngZelle.Value
Gruß Werner
Anzeige
AW: Kann man mit Makro Zellformat ändern?
31.01.2017 11:36:31
Jenny
Hallo Werner,
sorry das ich mich anscheinend unklar ausgedrückt habe.
Das bisherige Format in den Spalten C, J und K ist Datum TT.MM.JJJJ
Wenn die SVERWEIS Formel in Spalte C nun eine negative Zahl findet, kann sie es nicht als Datum ausgeben, da Excel keine negativen Daten kennt.
Die Formeln in den Spalten H bis L sind aber abhängig von C und können nur berechnet werden wenn in C eine Zahl steht, egal ob sie nun als Zahl oder Datum formatiert ist.
Wenn ich also erst am Ende des Makros die Formatierung ändere, können die Formeln in H bis L nicht berechnet werden und es wird Murks a la #NV! eingefügt.
Mit anderen Worten, das Format muss bereits beim Berechnen geändert werden, nicht erst am Schluss.
Gruß und danke für die viele Mühe
Jenny
Anzeige
Warum ist das so, ...
31.01.2017 15:50:15
Luc:-?
…Jenny?
Das kann doch nur an deinem, uns unbekannten(!), GesamtProgramm liegen. Zwar kann Xl negative Zahlen nicht als Datum formatieren, VBA aber ein TextDatum aus einer solchen Zahl erzeugen:
 ABCDEFG
20
DatumstextTage ab 30.12.1899=0→Datumstext→TageFormeln (mit UDFs)1849-09-03-18380 1849-09-03-18380B21:=Text2Date(A21)1873-01-28-9832 1873-01-28-9832D21:=N2Tx(B21;"JJJJ-MM-TT")1874-02-03-9461 1874-02-03-9461E21:=T2Nb(D21) 
21
22
23

🙈 🙉 🙊 🐵 Gruß, Luc :-?
Besser informiert mit …
Anzeige
AW: Warum ist das so, ...
31.01.2017 16:26:32
Jenny
Hallo Luc,
so habe erstmal lange gerätselt weshalb du meinen Namen anzweifelst und hab dann dank ein wenig suchen, gesehen dass es einen ähnlcihen Fall gab, gar nicht mal solang her.
Aber um es kurz zu machen, das eine hat mit dem anderen nichts zu tun.
In meinem Fall gibt es keine mit UDF's umgerechneten Daten.
Ich hab einfach eine Spalte C mit SVERWEIS Formeln, die das Zellformat DAtum haben. Und ich brauche halt für die negativen Ergebnisse der SVERWEIS Formel das Format Zahl.
Und ja ich weiß da gibt es eine Überschneidung mit dem anderen Fall.
Klar kann ich hingehen und bei jeder betroffenen Zelle händig das Zellformat ändern, dann funktionieren auch alle weiteren Formeln. Nur ich dachte, wenn das durch das Makro gleich mitbeachtet wird, kann ich mir das zukünftig bei Neueinträgen in die Tabelle sparen.
Gruß
Jenny
Anzeige
Das hast du wohl falsch verstanden, ...
01.02.2017 00:58:03
Luc:-?
…Jenny;
ich habe nicht deinen Namen angezweifelt, sondern in meinem Betreff einen Fragesatz begonnen. Den schließt man für gewöhnlich mit einem ? ab… ;-)
Du hattest geschrieben, Das bisherige Format in den Spalten C, J und K ist Datum TT.MM.JJJJ Wenn die SVERWEIS Formel in Spalte C nun eine negative Zahl findet, kann sie es nicht als Datum ausgeben, … Daraus habe ich geschlossen, dass es primär um Datums­werte geht. Allerdings kann C schlecht einheitlich als Datum formatiert sein, wenn hier auch negative Werte auftreten können. Da ich nicht weiß, warum das so ist, habe ich eine mögliche Ursache angenommen → es sind Datumswerte vor 1900. Falls das nicht so ist, hast du einen schlechten TabAufbau. Viell solltest du den auch mal näher erläutern, damit wir nicht sinnlos Zeit vergeuden.
Du scheinst hier einen leider sowohl typischen als auch häufigen ProjektEntwickler-Fehler zu machen: Du hast bereits eine Lösung angedacht und fragst nun nur danach, wie diese umgesetzt wdn kann. Das ist aber nicht dein eigentliches Problem, das so entwe­der gar nicht oder mehr schlecht als recht gelöst wird.
Morrn, Luc :-?
Anzeige
AW: Das hast du wohl falsch verstanden, ...
01.02.2017 10:21:40
Christian
Hallo Luc,
dann habe ich wohl wirklich etwas falsch verstanden. Sorry.
Ich bin nur leider heute beruflich sehr eingespannt, ich werde mich heut abend an eine Beispieltabelle machen, ok?
Gruß
Jenny
OK, Jenny-Christian! ;-] owT
01.02.2017 12:48:24
Luc:-?
:-?
AW: Das hast du wohl falsch verstanden, ...
03.02.2017 13:09:21
Jenny
Hallo Luc,
sorry das ich mich jetzt erst wieder melde, konnte nach einem Windows Update kaum noch meinen Laptop benutzen.
Da ist mir ein saudummer Fehler unterlaufen, ich weiß. Ich hatte mich kurz davor intensiv mit dem anderen Fall beschäftigt, um mal zu schauen ob ich da irgendwas von gebrauchen kann, war in Gedanken. Sollte nicht passieren.
Ich hoffe du hilfst mir trotzdem weiterhin. Denn ich hab die Bsp Datei erstellt.
Du siehst die Spalten C, J und K, die als Datum formatiert sind.
Sobald ich etwas an Spalte E ändere, greift das Makro, kopiert die Formeln aus Zeile 1 in die Zeile in der ich was geändert habe und fügt den berechneten Werte ein.
Aber überall wo jetzt die vielen # zu sehen sind, wird versucht, eine negative Zahl als Datum darzustellen was Excel nunmal nicht kann.
Jetzt habe ich 2 Möglichkeiten, händig bei einer sehr großen Tabelle von mehreren 1000 Zeilen die Zellformate anzupassen, oder dem Makro zu sagen, wenn du doch schon die Formel neu berechnest dann ändert doch grad das Zellformat entsprechend so, dass die negative Zahl angezeigt wird.
So brauch ich nur noch den Bereich E2 bis zum Ende zu kopieren und wieder einzufügen und ich habe alle Zellformate angepasst. Und wenn neue Zeilen hinzukommen wird es auch direkt angepasst.
Verstehst du jetzt was ich meine?
Gruß
Jenny
https://www.herber.de/bbs/user/111145.xlsm
Anzeige
AW: Das hast du wohl falsch verstanden, ...
04.02.2017 14:53:26
Werner
Hallo Jenny,
meinst du so was?

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


Gruß Werner
100% perfekt
04.02.2017 18:21:24
Jenny
zumindest soweit ich getestet habe.
Hallo Werner,
danke erstmal dass du mir trotz all den Diskussionen nach wie vor geholfen hast.
Wollte erst auf einen Fehler aufmerksam machen, aber dann ist mir dank reiflicher Überlegung aufgefallen, dass der Fehler gar nicht an deinem Makro lag, sondern ich ein falsches Ergebnis erwartet aber das Makro das richtige Ergebnis ausgegeben hatte, war also ein Denkfehler von mir.
Jetzt schau ich mir mal noch das Makro an, ob ich da vlt. noch was nicht verstehe, vlt. hab ich dazu dann noch ne Frage, aber so wie es im Moment ausschaut, funktioniert es.
Vielen Dank
Jenny
Anzeige
Gerne u. Danke für die Rückmeldung. o.w.T.
04.02.2017 19:00:40
Werner
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 12:44:19
Jenny
Wenn es nach mir ginge würde ich ja am liebsten noch viel mehr an der Tabelle automatisieren, habe mich aber bislang noch nicht getraut euch zu fragen weil ich befürchte, das läuft dann aufgrund des Aufwands als Auftragsprogrammierung.
LG
Jenny
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 14:24:07
Werner
Hallo Jenny,
fragen kostet erst mal nichts. Bevor du nicht dnach gefragt hast, kann dir auch niemand sagen, ob der Aufwand für ein Forum zu groß wäre.
Gruß Werner
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 14:32:27
Jenny
Hallo Werner,
dann erstmal danke für das Angebot,
werde dann mal die nächsten Tage beim Arbeiten notieren, was m.E. Sinn macht zu automatisieren und mich nochmal melden. Es geht mir da vor allem darum, dass sich die Tabelle2 (Datenquelle csv Datei aus Internet) regelmäßig aktualisiert und ich dann sehr viele Anpassungen händig an der Tabelle1 vornehme, um Tabelle1 auf dem neusten Stand der Dinge zu halten. Davon lässt sich bestimmt auch einiges per Makro machen.
Gruß
Jenny
Anzeige
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 15:24:35
Jenny
Hallo Werner,
ok, ich setze mich doch mal hin und versuche zusammenzuschreiben was ich mir vorstelle, es bezieht sich alles auf Tabelle1 und soll in dieser Reihenfolge geschehen
1. es müssten 3 Dinge bestimmt werden
1.1. die letzte Zeile insgesamt, z.Zt. 13150
1.2. die letzte Spalte insgesamt z.Zt. AQ
1.3. die Spalte D ist zweigeteilt, der erste Teil bis zur Zeit einschließlich Zeile 13014 ist leer, die Zellen D13015 bis D13150 enthalten Text. Die 13014 als letzte Zeile ohne Inhalt in Spalte D müsste bestimmt werden.
2. Die Formel =VERKETTEN2(M1:AQ1;";") soll in den Bereich D1:D13014 kopiert werden und danach die Werte eingefügt werden, wobei AQ und D13014 natürlich die grenzen sein sollen die vorher bestimmt wurden
Die UDF Verketten2 habe ich bereits, das braucht dich nicht zu kümmern.
zur Info das ist die UDF
Function Verketten2(ByRef bereich As Range, Trennzeichen As String) As String
Dim rng As Range
For Each rng In bereich
If rng  "" Then
Verketten2 = Verketten2 & rng & Trennzeichen
End If
Next
If Len(Verketten2) > 0 Then _
Verketten2 = Left(Verketten2, Len(Verketten2) - Len(Trennzeichen))
End Function
3. Der Inhalt aller Spalten von M aufwärts (also z.Zt. bis AQ) soll gelöscht werden.
4. Die Tabelle soll sortiert werden, Reihenfolge Spalte E aufsteigend, dann Spalte A aufsteigend.
5. In M1 soll die Formel =D1&WENN(UND(A1=A2;E1=E2);";"&M2;"") eingefügt werden, die Formel bis zum bitteren Ende also z.Zt Zeile 13150 kopiert werden und Werte eingefügt werden.
6. Es sollen Duplikate entfernt werden, wieder Spalten A und E als Kriterium, analog zu der Duplikate entfernen Funktion, die man unter Daten-Datentools findet.
7. In Spalte M soll der Text wieder aufgeteilt werden, analog zu der Text in Spalten Funktion die man unter Daten-Datentools findet, mit dem Semikolon als Trennzeichen.
8. Die Tabelle soll wieder sortiert werden, diesmal nach den Kriterien Spalte F absteigend, dann Spalte C aufsteigend.
9. Es soll ähnlich wie im Sub SpalteE, die Formeln in B1, C1, F1, H1, I1, J1, K1 und L1 bis zum Ende der Tabelle kopiert werden, also z.Zt Zeile 13150 und wie es das Sub SpalteE auch schon macht ab Zeile 2 die Werte eingefügt und die Formeln in Zeile 1 stehen gelassen werden. Hier nochmal das Sub SpalteE.

Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
If Cells(lngR, 3) > 0 Then
Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 3).Value  0 Then
Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 10).Value  0 Then
Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 11).Value
10. Der komplette Inhalt der Spalte D soll gelöscht werden.
11. Fertig.
Haltet ihr das noch im Rahmen des machbaren?
Gruß
Jenny
Anzeige
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 16:03:24
Werner
Hallo Jenny,
ein paar Fragen:
1. es müssten 3 Dinge bestimmt werden
1.1. die letzte Zeile insgesamt, z.Zt. 13150
Gibt es eine fixe Spalte die definitiv bis zum Schluß befüllt ist, wenn ja welche?
1.2. die letzte Spalte insgesamt z.Zt. AQ
Gibt es eine fixe Zeile die definitiv durchgehend von Spalte A bis zur letzten Spalte belegt ist, wenn ja welche?
1.3. die Spalte D ist zweigeteilt, der erste Teil bis zur Zeit einschließlich Zeile 13014 ist leer, die Zellen D13015 bis D13150 enthalten Text. Die 13014 als letzte Zeile ohne Inhalt in Spalte D müsste bestimmt werden.
Dazu folgenden Code:
Public Sub erste_belegte()
Dim rngBereich As Range
Dim loErste As Long
loErste = Sheets("Tabelle1").Range("D1").End(xlDown).Row - 1
MsgBox loErste
' Range über den freien Bereich definieren
With Sheets("Tabelle1")
Set rngBereich = .Range(.Cells(1, 4), .Cells(loErste, 4))
MsgBox rngBereich.Address
End With
Das funktioniert aber nur, wenn in dem Bereich keine Formeln sind, die ggf. als Ergebnis einen Leerwert ausgeben.
Gruß Werner
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 16:25:33
Jenny
Hallo Werner,
beantworte deine Fragen natürlich gerne.
Spalten A und E sind definitiv am Schluss und auch sonst lückenlos gefüllt.
zur zweiten Frage, diese Zeile gibt es leider nicht, da durch das neusortieren sich die Zeile ändert.
Wenn es zwingend einen Fixpunkt braucht, ist das einzige was mir einfällt, dass es die einzige Zeile ist, in der in Spalte A der Text nm0595485 steht und in Spalte E der Text tt0623889
Alle anderen Zeilen hören schon vor AQ auf.
Formeln gibts in der Tabelle ausschließlich in Zeile 1, es sei denn das Sub Spalte E wird grad ausgeführt, das ja vorübergehend noch mehr Formeln einfügt.
Gruß
Jenny
Nachtrag
05.02.2017 16:50:48
Jenny
Nein durchgehend gefüllt gibt es keine Zeile, da in Spalte D ja kaum was steht, die Zeile die bis AQ geht, hat ne Leerzelle in Spalte D und L.
Gruß
Jenny
AW: Nachtrag
05.02.2017 19:57:24
Werner
Hallo Jenny,
wenn dein Datenbereich nicht durch eine komplett leere Zeile oder durch eine komplett leere Spalte unterbrochen ist, dann kannst du die letzte Zeile sowie die letzte Spalte im Bereich mit CurrentRegion ermitteln.
Public Sub aaa()
Dim loLetzteSpalte As Long
Dim loLetzteZeile As Long
loLetzteSpalte = Sheets("Tabelle1").Range("A1").CurrentRegion.Columns.Count
loLetzteZeile = Sheets("Tabelle1").Range("A1").CurrentRegion.Rows.Count
MsgBox "Letzte Spalte: " & loLetzteSpalte
MsgBox "Letzte Zeile: " & loLetzteZeile
End Sub
Gruß Werner
AW: Nachtrag
05.02.2017 20:02:17
Jenny
Hallo Werner,
mir ist leider doch noch ein Problem mit deinem Sub Spalte E aufgefallen.
Die SVERWEIS Formel in Spalte C sucht ja das was in Spalte A steht in Tabelle2.
Wird es nicht gefunden, wurde in meinem alten Makro #NV ausgegeben, dein Makro lässt die Zelle leer.
Kann man das wieder ändern?
Und natürlich das mit den positiven und negativen Zahlen trotzdem beibehalten?
Und zu deinem letzten Beitrag, nein es gibt weder komplett leere Zeilen noch komplett leere Spalten.
Gruß
Jenny
AW: glaub ich eher nicht
06.02.2017 14:47:28
Werner
Hallo Jenny,
die paar CodeZeilen die ich da eingefügt haben löschen absolut nichts, sie ändern lediglich das Zellformat.
Gruß Werner
AW: glaub ich eher nicht
06.02.2017 15:16:55
Jenny
Hallo Werner,
damit du siehst was ich meine, nehm mal die Beispieltabelle mit der du das Makro geändert hast,
geb mal in A48 etwas ein, was der SVERWEIS in Spalte C nicht in tabelle2 findet, z.B. abc
und dann noch irgendwas in E48, damit das Makro startet.
dann erscheint in C48 #NV und J48 und K48 bleiben leer.
Wenn du jetzt deine Änderungen im Makro ausklammerst, also wie unten, erscheint auch in J48 und K48 jeweils #NV.
Erklären kann ich mir es auch nicht.
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
'Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
'If Cells(lngR, 3) > 0 Then
'    Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
'#ElseIf Cells(lngR, 3).Value  0 Then
'    Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 10).Value  0 Then
'    Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 11).Value

AW: glaub ich eher nicht
06.02.2017 16:22:06
Werner
Hallo Jenny,
kann ich im Moment nicht testen. Habe hier keinen Zugriff auf die Datei und kann auch keine .xlsm herunterladen.
Wenn, dann müsstest du mir deine Beispielmappe mochmals als .xlsx zur Verfügung stellen.
Ansonsten frühestens am Mittwoch.
Gruß Werner
AW: glaub ich eher nicht
06.02.2017 18:21:29
Jenny
Hallo Werner,
kein Problem.
Habe in meinem Beispiel hier in A48 abc geschrieben, also einen Text, der nicht in Tabelle2 steht, also auch vom SVERWEIS nicht gefunden wird.
Und in E48 def geschrieben, damit das Makro startet. In Zeile 48 steht jetzt das Ergebnis mit diesem abgeänderten Sub Spalte E: 
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
'Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
If Cells(lngR, 3) > 0 Then
Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 3).Value  0 Then
Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 10).Value  0 Then
Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 11).Value

In Zeile 49, habe ich dasselbe gemacht, nur das ich deine hinzugefügten Zeilen im SubE ausgeschlossen habe, und du siehst in J49 und K49 steht #NV
Das ist das Sub Spalte E, das in Zeile 49 benutzt wurde 
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
'Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
'If Cells(lngR, 3) > 0 Then
'    Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 3).Value  0 Then
'    Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 10).Value  0 Then
'    Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 11).Value
https://www.herber.de/bbs/user/111255.xlsx
AW: Gerne u. Danke für die Rückmeldung. o.w.T.
05.02.2017 16:14:51
Jenny
Hallo Werner,
ok, ich setze mich doch mal hin und versuche zusammenzuschreiben was ich mir vorstelle, es bezieht sich alles auf Tabelle1 und soll in dieser Reihenfolge geschehen
1. es müssten 3 Dinge bestimmt werden
1.1. die letzte Zeile insgesamt, z.Zt. 13150
1.2. die letzte Spalte insgesamt z.Zt. AQ
1.3. die Spalte D ist zweigeteilt, der erste Teil bis zur Zeit einschließlich Zeile 13014 ist leer, die Zellen D13015 bis D13150 enthalten Text. Die 13014 als letzte Zeile ohne Inhalt in Spalte D müsste bestimmt werden.
2. Die Formel =VERKETTEN2(M1:AQ1;";") soll in den Bereich D1:D13014 kopiert werden und danach die Werte eingefügt werden, wobei AQ und D13014 natürlich die grenzen sein sollen die vorher bestimmt wurden
Die UDF Verketten2 habe ich bereits, das braucht dich nicht zu kümmern.
zur Info das ist die UDF
Function Verketten2(ByRef bereich As Range, Trennzeichen As String) As String
Dim rng As Range
For Each rng In bereich
If rng  "" Then
Verketten2 = Verketten2 & rng & Trennzeichen
End If
Next
If Len(Verketten2) > 0 Then _
Verketten2 = Left(Verketten2, Len(Verketten2) - Len(Trennzeichen))
End Function
3. Der Inhalt aller Spalten von M aufwärts (also z.Zt. bis AQ) soll gelöscht werden.
4. Die Tabelle soll sortiert werden, Reihenfolge Spalte E aufsteigend, dann Spalte A aufsteigend.
5. In M1 soll die Formel =D1&WENN(UND(A1=A2;E1=E2);";"&M2;"") eingefügt werden, die Formel bis zum bitteren Ende also z.Zt Zeile 13150 kopiert werden und Werte eingefügt werden.
6. Es sollen Duplikate entfernt werden, wieder Spalten A und E als Kriterium, analog zu der Duplikate entfernen Funktion, die man unter Daten-Datentools findet.
7. In Spalte M soll der Text wieder aufgeteilt werden, analog zu der Text in Spalten Funktion die man unter Daten-Datentools findet, mit dem Semikolon als Trennzeichen.
8. Die Tabelle soll wieder sortiert werden, diesmal nach den Kriterien Spalte F absteigend, dann Spalte C aufsteigend.
9. Es soll ähnlich wie im Sub SpalteE, die Formeln in B1, C1, F1, H1, I1, J1, K1 und L1 bis zum Ende der Tabelle kopiert werden, also z.Zt Zeile 13150 und wie es das Sub SpalteE auch schon macht ab Zeile 2 die Werte eingefügt und die Formeln in Zeile 1 stehen gelassen werden. Hier nochmal das Sub SpalteE.

Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
If Cells(lngR, 3) > 0 Then
Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 3).Value  0 Then
Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 10).Value  0 Then
Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 11).Value
10. Der komplette Inhalt der Spalte D soll gelöscht werden.
11. Fertig.
Haltet ihr das noch im Rahmen des machbaren?
Gruß
Jenny
AW: Das hast du wohl falsch verstanden, ...
06.02.2017 18:19:26
Jenny
Hallo Werner,
kein Problem.
Habe in meinem Beispiel hier in A48 abc geschrieben, also einen Text, der nicht in Tabelle2 steht, also auch vom SVERWEIS nicht gefunden wird.
Und in E48 def geschrieben, damit das Makro startet. In Zeile 48 steht jetzt das Ergebnis mit diesem abgeänderten Sub Spalte E:
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
'Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
If Cells(lngR, 3) > 0 Then
Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 3).Value  0 Then
Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 10).Value  0 Then
Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
ElseIf Cells(lngR, 11).Value

In Zeile 49, habe ich dasselbe gemacht, nur das ich deine hinzugefügten Zeilen im SubE ausgeschlossen habe, und du siehst in J49 und K49 steht #NV
Das ist das Sub Spalte E, das in Zeile 49 benutzt wurde
Sub SpalteE(ByVal Target As Range)
Dim lngR As Long
lngR = Target.Row
'Cells(lngR, 2).FormulaR1C1 = Cells(1, 2).FormulaR1C1
Cells(lngR, 3).FormulaR1C1 = Cells(1, 3).FormulaR1C1
'If Cells(lngR, 3) > 0 Then
'    Cells(lngR, 3).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 3).Value  0 Then
'    Cells(lngR, 10).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 10).Value  0 Then
'    Cells(lngR, 11).NumberFormat = "DD.MM.YYYY"
'ElseIf Cells(lngR, 11).Value
https://www.herber.de/bbs/user/111255.xlsx
Was ich gemeint hatte und du wohl immer ...
05.02.2017 01:49:08
Luc:-?
…noch nicht verstanden hast, Jenny,
ist, warum verwendest du überhaupt Zahlen auf Blatt2? Es geht doch sowohl bei den negativen als auch den positiven stets um Datumswerte, die positiven ab 1900, die negativen davor. Es ist bei einer solchen Datenlage einfach leichtsinnig, Xl-Datumswerte zu verwenden, denn die passen erst ab dem 01.03.1900 zu den realen Kalender­Daten; ein Grund weshalb Xl auch Datums­wert­Formatierung ab 1904 anbietet! VBA ist hier genau und nimmt deshalb den 1.1.1900 mit Tag2 an. Tag1 wäre demzufolge der 31.12.1899 und sein Vortag Tag0. Negativ wird die Tages­Zählung dann ab dem 29.12.1899 (Tag -1). Falls die sich daraus erge­ben­den Zahlen zu deinen Negativ­Werten passen, kannst du alle Datums­werte (Tages­Zähler, in Blatt2) mit einer einfachen, VBA-basier­ten UDF in Text-Datums­Angaben umrechnen. Mit denen hätte dann der SVERWEIS (auf Blatt1) auch kein Problem und du bräuch­test den ganzen Format­Umstellungs­Zirkus erst gar nicht! Außerdem würde der Betrachter gleich sehen, worum es geht, und müsste nicht erst umrechnen.
Ich finde, du bist mit dem Ganzen auf dem Holzweg. Falls aber erst ein nach­geordnetes Pgm das alles umrechnen soll, solltest du das Gesamt­Konzept auf seine Effizienz und Sinn­haftigkeit über­prüfen! Genau das hatte ich auch mit deinem eigent­lichen Problem gemeint…! ;->
Auszug Blatt2:
 ABC
1
nm000001772727.12.1901nm00000301071704.05.1929nm0000315251014.11.1906nm00005111253324.04.1934nm00010211800820.04.1949nm0001256208818.09.1905nm00027691414623.09.1938nm00071621191716.08.1932nm0007229305108.05.1908nm0016507  nm00503181801729.04.1949nm0073838  nm0106267-461512.05.1887nm0138898  nm01401531195220.09.1932nm0140460510825.12.1913nm0172237997119.04.1927nm0214942-118501.10.1896nm0226773891427.05.1924nm0237693-354318.04.1890
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Auszug Blatt1:
 ABCD
1
nm0832458 18.03.1892tt0003973nm0237693 18.04.1890tt0003973nm0000315 14.11.1906tt0018737nm0730812 26.07.1906tt0018737nm0000017 27.12.1901tt0021156nm0816123 23.08.1898tt0021156nm0007229 08.05.1908tt0022183nm0927236 03.01.1908tt0022183nm0777105 11.08.1906tt0022183nm0772324 26.09.1898tt0022183nm0880946 29.01.1879tt0022183nm0172237 19.04.1927tt0024481nm0949515 03.09.1913tt0024481nm0001256 18.09.1905tt0024481nm1066799  tt0041137nm0073838  tt0042803nm0647130  tt0042803nm0007162 16.08.1932tt0042803nm0800386 23.04.1910tt0042803nm0275428 29.10.1907tt0042803
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Die Fml in Blatt1/SpalteC kann dann einfach so lauten: =SVERWEIS(A1;Tabelle2!A:C;3;FALSCH)
Die Umrechnung in Blatt2/SpalteC habe ich mit dieser Fml vorgenommen: =WENN(ISTLEER(B1);"";N2Tx(B1;"TT.MM.JJJJ";""))
Da die UDF N2Tx bisher unveröffentlicht ist (müsste sie zuvor auf Abhängigkeiten kontrollieren, wozu ich momentan keine Zeit habe), sollte dir dafür die UDF aus dem anderen BT genügen, denn die kann das hier Gezeigte auch (ist nur nicht so universell wie N2Tx).
Luc :-?
AW: Was ich gemeint hatte und du wohl immer ...
05.02.2017 08:41:22
Jenny
Hallo Luc,
danke erstmal für die ausführliche Erklärung, du hast selbstverständlich recht, ein ausgeschriebenes Datum macht natürlich mehr Sinn als eine negative Zahl.
Da ich aber diese UDF nicht vorliegen habe, kann ich jetzt nur mutmaßen.
Mein Ziel ist ja, das Spalte C eine Grundlage für weitere Formeln ist. Wenn deine Ausgabe ein Text ist, übergeht die KGRÖSSTE Formel in Spalte K diesen Text, oder verstehe ich da etwas falsch?
Der Zählenwenn Formel in Spalte J dürfte das Zellformat ja egal sein oder?
Was ich jetzt nicht überprüfen kann ist ob das was deine UDF ausgibt auch eine Zahl ist.
Andere Frage, sortieren nach Alter in Spalte C, ist mit meiner Version problemlos möglich, wie sieht es da bei deiner Version aus?
Noch eine andere Frage, gibt =SVERWEIS(A1;Tabelle2!A:C;3;FALSCH) nicht 0 aus, wenn die gefundene Zelle leer ist?
Mit der 0 hate ich irgendwann mal Probleme bekommen, daher hab ich die Wenn Formel drumrumgemacht.
Gruß
Jenny
Mal auf die Schnelle, später ggf mehr, ...
05.02.2017 15:28:43
Luc:-?
…Jenny;
das Problem besteht hierin:
 HIJK
23
Xl-1904-Datum als Xl-Tageszähler… als TextDatumsformatxl-formatiertunformatiertVBA-Realdatum-30.12.3703##########-657435 -29.12.3703##########-65743401.01.0100-02.01.1904##########-129.12.189901.01.190400.01.1900030.12.189902.01.190401.01.1900131.12.189903.01.190402.01.1900201.01.190029.02.190428.02.19005927.02.190001.03.190429.02.19006028.02.190002.03.190401.03.19006101.03.1900TT.MM.JJJJTT.MM.JJJJStandard← Format(hier als Text)Formel in K25[:K33]:=N2Tx(J25;"TT.MM.JJJJ")
24
25
26
27
28
29
30
31
32
33
34
35
Mit VBA kommt man also bis zum 1.1.100 zurück, dann erst ist Schluss. Da Xl eine derartige Formatierung nicht vorsieht, sind das natürlich Datumstexte, die für die von dir geplanten Berechnungen aber in Zahlen rückverwandelt wdn könnten. Es muss dann nicht als Datum formatiert wdn, sondern die Rückrechnung in die Fmln eingebaut oder das Ganze mit einer VBA-Subprozedur erle­digt wdn.
Gruß, Luc :-?
AW: Mal auf die Schnelle, später ggf mehr, ...
05.02.2017 16:45:22
Jenny
Hallo Luc,
das ist zwar lieb, dass du dir all diese Mühe machst.
Jedoch wir reden hier von 24 negativen Zahlen von insgesamt 4717 Daten in Tabelle2.
Diese Anzahl ist sehr sehr gering und ich habe falls ich doch mal das richtige ausgeschriebene Datum brauche, in wenigen Sekunden die Internetseite geöffnet, wo die Daten herstammen.
Ich weiß nicht ob es wirklich notwendig ist, diesen ganzen Aufwand zu treiben, für die wenigen Ausnahmefälle in der Tabelle.
Rechnen lässt sich auch mit den Daten wie ich sie jetzt habe hervorragend.
Gruß
Jenny
Nur sind die dann ggf falsch formatiert, ...
05.02.2017 17:21:54
Luc:-?
…Jenny;
außerdem ist der Aufwand für die WechselFormatierung mit Sicherheit größer und müsste außerdem alles, was <61 ist, als Zahl belassen, <0 bzw ≤0 reicht da nicht. Und natürlich ist das auch noch nutzer­unfreundlich!
Ich habe den Eindruck, dass du das Problem nicht siehst bzw sehen willst. Wer mit historischen Daten arbeitet und diese dann der Einfachheit halber von Xl als als Datum formatierten Tageszähler interpretieren lässt, kann sich schnell Fehler einhandeln.
Nebenbei, wie kommst du denn überhaupt zu den negativen Tageszählern? Die müssen doch auch schon berechnet worden sein. Eindeutig sind die dann auch nur, wenn das mit VBA geschehen ist.
Luc :-?
...Und genau diese Kalenderabweichung ...
05.02.2017 17:32:55
Luc:-?
…berücksichtigt auch Werners Pgm nicht, Jenny,
wodurch Xl dir dann ggf falsche Datumsdarstellungen liefert, was du dann nicht mal bemerken und deshalb auch nicht auf dieser ominösen WebSite nachsehen würdest. Aber meinetwegen, macht mal, ist ja nicht mein Schaden…
Luc :-?
AW: ...Und genau diese Kalenderabweichung ...
05.02.2017 18:09:07
Jenny
Wie ich gerade eben schon gesagt habe, da habe ich echt was misverstanden und es tut mir auch leid.
Aber es gab auch keinen Link zu einer ominösen Webseite oder hab ich den auch übersehen?
Ich gehe davon aus, du sprichst Werners Änderung am Sub Spalte E an.
Tu mir bitte den Gefallen ändere es dann so ab, dass es funktioniert.
Wenn es notwendig ist, dann auch das Makro, dass ursprünglich bei Christians Beitrag gepostet wurde.
Gruß
Jenny
AW: Nur sind die dann ggf falsch formatiert, ...
05.02.2017 18:05:14
Jenny
Wie ich darauf komme?
zuerst hab ich einfach auf beide Jahre 1000 Jahre draufgepackt , also Datedif zwischen dem 01.01.2900 und dem tausend Jahre späteren Datum von dem das ich umwandeln will und dann halt die Differenz genommen.
Dann hab ich deine Diskussion mit Christian gelesen und hab die das ganze so abgeändert dass der 31.12.1899 der Tag 0 ist, mit anderen Worten ich hab zu meinen Ergebnissen noch 1 addiert. Ganz im Endeffekt hab ich dann auch inzwischen das Makro genommen das bei Christian angesprochen wurde.
Aber du hast recht, ich verstehe deine Einwände wohl wirklich nicht, obwohl wenn ich sie versehe müsste es Probleme mit den Daten zwischen dem 1.1.1900 und dem 2.3.1900 geben also was zwischen 1 und 60 liegt. Das Problem ist mir wohl nicht aufgefallen, da es in der Liste keine Daten zwischen -56 und dem 29.06.1901 gibt, also nichts was diesen Bereich betrifft. Aber ich schließe es auch nicht aus das das noch kommt.
Es tut mir leid, das es den Eindruck gemacht hat das deine Mühe mir egal ist. Ich habe es offensichtlich wirklich missverstanden.
Gruß
Jenny
Christian hatte seinen eigenen Zähler, ...
05.02.2017 20:12:51
Luc:-?
…Jenny,
was aber unnötig umständlich ist, da VBA das Problem der 1900-Datumswerte* vor Tag 61 (=1.3.1900) ja schon berück­sichtigt. Du kannst also die Datumstexte von deiner (nicht meiner!) „ominösen“ Quelle im Internet auch als solche kopieren und dabei aber nicht von Xl automatisch umwandeln lassen! Dafür benutzt du dann VBA, wenn's benötigt wird, bspw in (KKLEINSTE-)Fmln. Hierfür kannst du die folgenden beiden Trivial-UDFs verwenden: 
Function Text2Date(Datum$): Text2Date = CDate(Datum): End Function
Function Date2Text(DatTZ&): Date2Text = Format(DatTZ, "dd.mm.yyyy"): End Function
Die 1. liefert nur dann einen Fehler­wert, wenn der Datumstext nach dem 31.12.9999 liegt. Bei Datumstexten vor dem 1.1.100 wdn evtl Vor­nullen ignoriert und die Inter­pre­tations­Auto­matik für 2stellige Jahres­zahlen aktiv. Die 2.UDF liefert in beiden Fällen Fehler­werte also bei Zahlen <-657 434 und solchen >2 958 465. Aber der­artige Daten dürften bei dir ja auch nicht vor­kommen. Die UDFs sind aber nicht matrix(formel)fähig, d.h., sie können nur Einzel­werte kon­ver­tieren! Deine KKLEINSTE-Fmln müssen also (für die Umwandlung in derselben Fml) immer Einzel­werte zurück­geben und können dann auch nur bereits umge­wandelte (und bspw in einer Hilfs­spalte vor­lie­gende) Datum-Tages­zähler als Argu­ment ver­ar­beiten.
Du kannst natürlich auch beide Daten, das Textdatum und den daraus ermittelten Tages­zähler in einer Verbund­Zelle (über 2 Zellen) halten. Dann ist nur der Datums­text zu sehen, aber in der 2.Zelle liegt er in einen Zähler umge­wandelt vor. Auf diese Zweit­Zellen kannst du dann gezielt mit Fmln zugreifen, die eine Zahl benötigen. Das sähe dann so aus:
 IJK
30
28.02.190028.12.189931.12.189929.12.189928.12.189930.12.189930.12.189931.12.189929.12.189901.01.190001.01.190028.02.1900Muster-VbZelle f.Pinsel Fmln in J30[:J35]:=Text2Date(I30)K30[:K35]:=Date2Text(KKLEINSTE(J$30:J$35;ZEILE(A1)))
31
32
33
34
35
36
37
38
Du müsstest hierfür irgendwo eine entsprd formatierte Muster-Verbund­Zelle anlegen und deren Format mit dem Format­Pinsel auf die beiden Datums­zellen pro Zeile übertragen. Dann hast du quasi eine ver­steckte Hilfs­spalte zV.
________
* Die Lotus-Pgmmierer hatten vor ca 30 Jahren einen Fehler begangen und das Jahr 1900 wie im Julianischen Kalender als Schaltjahr gewertet. Wir verwenden aber schon seit Jahr­hun­derten den Gregorianischen Kalender, der den dadurch ent­stehenden astro­nomischen Fehler größten­teils kom­pensiert; danach war 1900 kein Schalt­jahr! MS hat diesen Fehler dann aus Kom­patibi­litäts­gründen - Lotus 1-2-3 war damals Markt­führer(!) - über­nommen, aber später nur in VBA bereinigt.
Luc :-?
AW: Christian hatte seinen eigenen Zähler, ...
05.02.2017 20:39:55
Jenny
Hallo Luc,
ich denke ich kann langsam vlt. etwas Licht ins dunkel bringen, ich sagte ja, ich habe die aktuellen negativen Zahlen auf dem Weg berechnet, der Christian geraten wurde
also habe ich meine Daten vor 1900 in dasselbe Format gebracht, wie er es hatte.
und dann die UDF 

Function Text2Date(Datum$): Text2Date = CDate(Datum): End Function
benutzt die bereits auch ihm geraten wurde.
Sämtliche Stichproben in meinen Berechnungen die negative Zahlen beinhalten jedenfalls scheinen richtig zu sein, genauso wie die negative Zahlen genau das wiedergeben, was sie sollen.
Gruß
Jenny
Er hatte aber spezielle ZählerWünsche, ...
06.02.2017 03:21:55
Luc:-?
…Jenny,
die nicht mit der TageszählerInterpretation durch VBA übereinstimmen. Das verursacht nur unnötigen zusätzlichen Aufwand und sollte deshalb besser nicht gemacht wdn, nur um zu erreichen, dass der 31.12.1899 Tag0 oder -1 ist, denn das war sein Ziel. Außerdem müssten dann ja auch die Tage vor Tag 61 korrigiert wdn…
Morrn, Luc :-?
AW: Er hatte aber spezielle ZählerWünsche, ...
06.02.2017 08:58:17
Jenny
Hallo Luc,
ich wende diese UDF ja auch nur auf Tage vor 1900 an, Tage nach 1900 stehen ja in meinem Fall automatisch als Datum in der Tabelle, ohne das es einer Formel bedarf.
Und wenn ich in meine Tabelle händig ein Datum aus dem Januar oder Februar 1900 in die Tabelle schreibe und das dann als Zahl formatiere kommt die korrekte Zahl heraus, genauso wie die korrekte Zahl herauskommt, wenn ich aus einem Datum im Dezember oder November 1899 diese UDF anwende kommt die für mich passende Zahl heraus. Diese Diskussion die du mit Christian geführt hast ist irrelevant ich brauche den 31.12.1899 als 0. Selbst wenn sich herausstellen sollte das das was ich da mache den 31.12.1899 als -1 darstellt, bekomme ich es noch hin bei dieser Formel 1 zu addieren.
Gruß
Jenny
Gruß
Jenny
Das ist doch alles unsinnig und verursacht ...
06.02.2017 14:34:35
Luc:-?
…nur zusätzlichen Aufwand und damit Fehlerquellen, Jenny;
wieso brauchst du den 31.12.1899 als 0? Wer will denn mit diesen Zahlen ≤0 arbeiten? Die benötigst du doch nur für Xl-Fktt, die nur mit Zahlen fktionieren, also im Hintergrund, der keinen bloßen Nutzer deines Erzeug­nisses inter­essiert!
Dann behauptest du, dass bei Tagen nach 1900 die richtige Zahl rauskommt — das stimmt nicht! Ich hatte das wiederholt ange­merkt und du scheinst es nicht begreifen zu wollen → vor dem 1.3.1900 ist kein Xl-Tageszähler richtig, denn Xl schiebt hier einen nicht­existenten 29.2.1900 ein. Folg­lich wird einem existenten 28.2.1900 die Zahl 59 zugeordnet, die dann von der Xl-Forma­tierung zwar auch als 28.2. angezeigt wird, aber trotz­dem nicht brauch­bar ist, weil ihr dann wg der Verschiebung (Tag 60 fehlt!) ein falscher Wochen­tag zuge­ordnet wird (Neujahr 1900 war an einem Montag, nicht Sonntag!). Hast du dir meine Bspp über­haupt ange­sehen? Daraus geht das doch eindeutig hervor! Grund­sätzlich gilt: Wird mit historischen Daten vor dem 1.3.1900 gearbeitet, sind die einschlä­gigen Xl-Standard-Fktt zur Datums­darstel­lung unbrauch­bar! In diesen Fällen sollte durch­gehend auf VBA ausge­wichen wdn, um fehler­anfäl­ligen Zusatz­Aufwand zu vermeiden. Dazu gehört auch, die von VBA verwendete Tages­zähler­Inter­pre­tation. Damit gibt's dann auch kein Problem mit der 0! Jede Manipulation am VBA-Tages­zähler macht weiter­gehende Aus­wertungen kompli­zierter! Warum nur willst du dir das unnötiger­weise auf­laden‽
Mein guter Rat ist: Verwende alle DatumsAngaben als Texte und wandle sie nur bei Bedarf intern mit VBA in Zahlen (VBA-Tages­zähler) um (und dann auch wieder zurück)! Benutze nicht die Xl-Datums­Formatierung!
Luc :-?
PS: Werner hat in diesem Zusammenhang wohl nur dein (reiz­volles) Sekundär­Problem inter­essiert (das du gar nicht haben müsstest!). Er hat nicht das getan, was allen Office-Beratern einge­trich­tert wird → Erfrage das eigent­liche (Primär-)Problem! Damit steht er nicht allein, denn das ist auch Hpt­Problem der Foren­Hilfe an sich! Im Falle von Christian war das etwas anders, denn der hat nicht erkennen lassen, was er wirklich beab­sichtigt hat, was sein evtl Sekundär- als Primär­Problem erschei­nen ließ.
AW: Das ist doch alles unsinnig und verursacht ...
06.02.2017 14:56:37
Jenny
Hallo Luc,
ok, jetzt habe ich dein Argument denke denke ich verstanden, Daten zwischen dem 1.1.1900 und dem 1.3.1900 werden von Excel nicht richtig als Zahl zwischen 1 und 60 interpretiert.
Dann ist mir auch klar, weshalb mir das nicht aufgefallen ist, in meiner Tabelle gibt es ein Datum am 4.11.1899 (dargestellt als -56) und das nächste erst wieder am 29.06.1901, also gibt es im Moment keine Daten, die von dem von dir beschriebenen Problem betroffen wären, da keines meiner Daten zwischen dem 1.1. und dem 1.3.1900 liegt.
Ich habe alles gelesen was du geschrieben hast, Wort für Wort, ich konnte es halt aus gerade genanntem Grund nicht korrekt nachvollziehen, für mich hat meine Tabelle funktioniert, da sie wie gesagt keine Daten in deinem genannten Bereich enthält.
Zu deinem Einwand, für Daten vor 1900 habe ich ja die in meinem letzten Post genannte UDF im Endeffekt genutzt, also VBA.
Zu meinen Absichten, die sind ähnlich wie Christians, Rangfolgen berechnen, nach Datum sortieren können, Altersunterschiede berechnen können
Zum Fall altersunterschiede wenn der 31.12.1899 der Tag 0 ist wie es im Moment als Zahl dargestellt wird, ist der Unterschied zum 1.1.1900 genau 1 Tag. vom 30.12.1899 als -1 zum 1.1.1900 genau 2 Tage usw. also passen die negativen Zahlen exakt zu dem was ich berechnen will.
Aber solche Rechnungen gehen halt nur, wenn auch wirklich eine Zahl vorliegt, ich sehe es eher als umständlich an, VBA im Bedarfsfall zu benutzen, um einen Text als Zahl darzustellen, wo ich doch sowieso eigentlich in jedem Fall eine Zahl brauche.
Um Text zu haben hätte ich auch die ganz ursprüngliche Formatierung a la 31.12.1899 die für Excel ja Text ist behalten können, deshalb habe ich hier ja gepostet, als ich Christians Beitrag gesehen habe und auf die Idee kam, für dich ist es ja auch einfacher, daraus Zahlen zu machen.
Und wie gesagt, da der Bereich vom 1.1.1900 bis 1.3.1900 in meiner Tabelle nicht vorkommt.
Die Altersunterschiede berechne ich bei Daten nach 1900 sowohl auf dem altbewährten Prinzip mit Datedif,
bei Daten wo eins vor und eins nach 1900 ist packe ich zu meiner negativen Zahl und meinem Datum 4000 Jahre also 1461000 Tage drauf
Als Beispiel Datedif zwischen dem 31.12.1900 und dem 3.2.1900 wäre dann =DATEDIF(0+1461000;"02.03.1900"+1461000;"D")
was den realen 62 Tagen entspricht
wenn ich den 28.2. eingebe kommt 59 raus, was auch stimmt da 1900 ein Schaltjahr war, also funktioniert meine Rechnung auch bei Daten aus dem Zeitraum 1.1. bis 1.3.1900
Aber wie gesagt solche Rechnungen kommen in der Tabelle einfach nicht vor, da keine Daten in diesem Zeitraum liegen
Gruß
Jenny
Das ist ein Irrtum, ...
06.02.2017 19:01:10
Luc:-?
…Jenny,
1900 war eben kein Schaltjahr! Das versuche ich dir schon die ganze Zeit klar zu machen! Du behauptest zwar, alles gelesen zu haben, hast es aber offensichtlich nicht, sonst würdest du jetzt das nicht schreiben. Das ist ja gerade der Xl-Datum1900-Fehler! Die Lotus-Pgmmierer hatten die Regeln des Julianischen*, nicht die des Gregorianischen Kalenders ange­wendet und MS hat das aus Kompatibilitäts­gründen beibehalten (nebenbei, die 1904-Datums­Einstellungs­Möglichkeit dient auch nur der Darstellung nega­tiver Zeiten, für Datumswerte ist das unsinnig).
* Nach dem berechnet heute noch die Orthodoxe Kirche ihre Feiertage, weshalb Weihnachten und Ostern mittlerweile (seit C.I.Caesars Zeiten!) dort um Wochen verschoben sind!
Luc :-?
AW: Das ist ein Irrtum, ...
06.02.2017 19:34:46
Jenny
Hallo Luc,
ja jetzt verstehe ich, erstmal ich habe alles gelesen, die Frage ob alles verstanden oder bei der Menge auch alles behalten ist eine andere.
Bevor wir hier noch weitere Diskussionen anstellen, die nicht zum Ziel führen, weil ich sie misverstehe.
Du hast meine Bsp. Tabelle, ändere sie so ab, das die negativen Zahlen und die Berechnungen auch trotz deiner Einwände stimmen.
Mir zu erklären wieso ich etwas so und so ändern soll scheint hier keinen Sinn zu machen
Auch wenn ich jetzt verstanden habe, das Excel fälschlicherweise von einem Schaltjahr 1900 ausgeht, ich weiß deshalb noch lange nicht, was ich deshalb mit meiner Tabelle tun muss um das zu berücksichtigen.
Wie gesagt meine Ziele sind mit ner Datedif Formel exakte Ergebnisse einer Differenz zwischen einem Datum vor und einem nach 1900 zu bekommen, nach den Daten sortieren zu können und halt die aus der Tabelle bekannten ZÄHLENWENN und KGRÖSSTE Formel, dass die korrekt funktionieren.
Und das so dass das mithilfe das SubE korrekt in die Tabelle 1 übertragen wird.
Gruß
Jenny
PS: Aber immer noch selbst wenn es dank des Jahr 1900 Problems eine Ergebnisverfälschung gibt, kann ich doch immer noch im Datedif einfach 1 abziehen, wenn ein Datum vor 1900 dabei ist.
Aber wie gesagt ich hab dir gesagt was ich damit machen will, ich bitte dich dann meine Bsp. Tabelle entsprechend zu ändern, ich habe leider den Überblick verloren was du mir sagen willst und sehe mich daher außer Stande meine Tabelle anzupassen.
Ich habe dir jetzt mal 2 nicht-triviale, neue ...
08.02.2017 01:54:26
Luc:-?
…UDFs zur Berechnung der Tageszähler lt VBA aus Datumstexten und wieder zurück in Datums­texte spendiert, Jenny;
was die genau machen und können, kannst du der Mappe entnehmen, die ich zu einer TestDatei für diese umfunk­tioniert habe. Darin sind dann auch einige Berechnungs­Bspp enthalten.
Da deine Mappe keine nennens­werte Struktur hat, konnte ich mich nur an den 2 Fml-Rudi­menten und dem, was du in deinen AWen an mich erwähnt hast, orien­tieren. Dafür habe ich die o.g. Bspp beige­steuert. Allerdings keine mit DateDif und deiner ganzen kompli­zierten und fehler­anfälligen Berechnung. Wie du an meinen Bspp sehen kannst, ist das mit VBA-Tages­zählern auch nicht erfor­derlich. Der Dif­ferenz ist es nämlich egal, wo die nega­tiven Werte auf­hören und die posi­tiven der Basis anfangen - eine richtig durch­geführte Sub­trak­tion liefert immer das reale Ergebnis!
Da dein SubE in der zugrunde­liegenden Datei nicht enthalten war und ich mich dafür auch nicht zuständig fühle, habe ich mich damit auch nicht befasst. Die o.g. UDFs sollten ohnehin fast jeden Wunsch im Rahmen ihrer Aus­richtung erfüllen können. Deshalb können sie auch oW auch in TeilFmln als Argument von KKLEINSTE (bzw KGRÖSSTE) ein­gesetzt wdn. ZÄHLENWENN hingegen verlangt stets Zell­Bereiche, so dass du ggf (Zwischen-)Ergeb­nisse hilfs­weise in solche speichern musst. Mit SVERWEIS kannst du die Daten auch pro­blemlos von einem Blatt ins andere über­nehmen, so dass du schon zwingen­dere Argumente für eine Auto­mati­sierungs­Lösung anführen müsstest…
Aber ich glaube doch, dass ich meinen Teil getan habe, und du mit den UDFs ein Werk­zeug hast, welches deine Probleme zum Großteil lösen helfen kann.
Die Datei kannst du hier herunterladen!
Morrn, Luc :-?
AW: Ich habe dir jetzt mal 2 nicht-triviale, neue ...
08.02.2017 09:08:39
Jenny
Hallo Luc,
du hast offensichtlich die falsche Bsp. Mappe genommen, diese hier, die Einträge in Zeile 48 und 49 enthält, habe ich absichtlichfür Werner ohne Makros erstellt und das Sub Spalte E in dem Beitrag gepostet.
Es gab noch eine Bsp. Mappe relativ am Anfang des Threads, glaub 111145.xlsm war es, die das Sub beinhaltet hatte.
Das erste was mir auffällt, aber das konntest du nicht wissen, die Daten vor 1900 die du berechnet hast, liegen einen Tag später als die, die ich eigentlich beabsichtigt hatte, mit der negativen Zahl auszudrücken.
Es gibt immer noch ein paar offene Fragen.
1. Ich will in Tabelle2, Spalte C ein neues Datum vor 1900 eintragen, in welchem Format muss ich das eintragen und welche Funktion brauche ich jetzt genau um das umzurechnen?
2. du schreibst, ich brauche Datedif nicht mehr, ich könnte einfach subtrahieren, ich nehme mal ein Beispiel, ich möchte jetzt den Unterschied zwischen dem 18.3.1892 und dem 10.8.1914 berechnen, in Jahren und Tagen, dass da also 22 Jahre 145 Tage herauskommt, (vorausgesetzt ich hab richtig gerechnet), was muss ich da in Zukunft tun?
3. Das nächste Thema von mir, nach der Spalte mit den Daten sortieren, damit war eigentlich die komplette Tabelle 1 gemeint, dass ich die sortieren kann, bislang mit meiner Lösung auch möglich, in deiner Lösung sehe ich jetzt nur Spalte G in Tabelle2 als Sortiermöglichkeit über eine Matrixformel.
Aber die größten Bauschmerzen bereitet mir etwas anderes,
du benutzt hier mehrere Matrixformeln um das ganze zu berechnen, das Sub Spalte E hat ja nur einen Zweck, Rechenzeit zu sparen, damit nicht permanent alle Formeln neu berechnet werden, das Ausführen vom Sub Spalte E für die komplette Tabelle1 dauert 13 Minuten.
Daher ist das Sub Spalte E so gestrickt, dass nur die Formeln neu berechnet werden, an denen sich auch wirklich etwas am Ergebnis ändert.
Wenn ich jetzt 3 Matrixformeln, die sich in der Originaltabelle über 13000 und später wenn ich mal fertig bin sicher 20000 oder mehr Zeilen erstrecken in der Tabelle hab, hab ich wieder eine riesige Berechnungezeit, jedes mal wenn ich auch nur eine einzige Zelle ändere.
Ich stehe jetzt also vor der Wahl, in Zukunft einen riesigen Aparrat an Berechnungen mit langen Berechnungszeiten und umständlicheren Wegen wie ich zum Ziel komme zu nutzen oder meinen Weg weiterzugehen, auch wenn er wenn Daten vor dem 1.3.1900 im Spiel sind, ungenau rechnet.
Aber das sind 24 von 13000 Zeilen und Daten die zwischen dem 1.1.1900 und dem 1.3.1900 liegen kommen überhaupt nicht vor.
Gruß
Jenny
Eine Empfehlung und zu deinen Fragen, ...
09.02.2017 04:47:59
Luc:-?
…Jenny;
ja, ich habe die letzte BspMappe genommen, weil ich annahm, dass die evtl schon mehr TabStruktur nebst Fmln zeigt. Aber du willst wohl auf eine voll­ständige Auto­mati­sierung hinaus, was deine Diskussion mit Werner ja schon angedeutet hatte. Ich weiß auch, dass in der 1.BspMappe VBA-Pgmm enthalten waren, aber mir war's ganz recht, dass die hier fehlen, so dass sich keine evtl Störung meiner UDFs durch Ereignis­Prozeduren ergeben kann.
Die Daten vor 1900 hatte ich um einen Tag korrigiert, da ich aus der bisherigen Diskussion schloss, dass du sie ggf bereits um einen Tag korri­giert haben könntest, was die Berechnung ver­fälscht hätte. Aber das hast du wohl doch nicht getan. Es ist auch wichtig, dass die VBA-Tages­zähler so bleiben, wie sie per VBA ermittelt wdn, denn der Datums­komplex ist in VBA völlig unab­hängig von Xl, d.h., beide Systeme passen nicht zusammen, weil sie sich einer­seits um den einen Tag vor Tag 61 (1.3.1900) und anderer­seits in der Behandlung von Daten vor dem 1.1.1900 unter­scheiden. Letztere sind in VBA bis zur Grenze 1.1.100 einfach einge­ordnet. Darauf ist auch die vbFkt DateDiff aus­ge­richtet, die auf diesen durch­gehend ermit­telten Zählern basiert. Ihr Tag Null ist aber der 30.12.1899. Das solltest du also nicht ändern, wie am fol­genden Fml­Bsp zu sehen ist (hier habe ich noch eine Demo-Trivial-UDF verwendet, um das anschaulich machen zu können):
 LMNOPQRST
39
Tage des/rvonbisXlVBAXl auf vbTZVBA auf vbTZSchalttageJahre19.Jhdts01.01.180131.12.1900--36 52499/12/3099-12-3024100Jahre 18..01.01.180031.12.1899--36 52499/12/3099-12-302410020.Jhdts01.01.190131.12.200036 52536 52599/12/3199-12-3125100Jahre 19..01.01.190031.12.199936 52536 52499/12/3099-12-3024100 O40[:O43]:=WENNFEHLER(N40-M40+1;"--")36 52436 5245 2171 200100 P40[:P43]:=TextToDate(N40)-TextToDate(M40)+136 52436 5245 2171 200100FormelnQ40[:Q43]:=TEXT(P40;"JJ/MM/TT")36 52536 5255 2171 200100 R40[:R43]:=DateToText(P40;;"jj-mm-tt")36 52436 5245 2171 200100 S40[:S43]:=REST(P40;365) u.T40[:T43]:=(P40-S40)/365dywmyyyyVBA.DateDiffP44[:P47]:=CalDiff(P$48;$M40;$N40)+1Q44[[:Q47];[R44[…T47]]]:=CalDiff(Q$48;TextToDate($M40)-1;$N40)
40
41
42
43
44
45
46
47
48
49
Der PgmCode dieser UDF sieht so aus: 

Function CalDiff(IntervallTyp$, Bezug1, Bezug2, Optional ByVal WochenBeginn As VbDayOfWeek, _
Optional ByVal JahresBeginn As VbFirstWeekOfYear)
CalDiff = DateDiff(IntervallTyp, Bezug1, Bezug2, WochenBeginn, JahresBeginn)
End Function
Außerdem kannst du auch die beiden nicht-trivialen UDFs noch etwas verbessern, indem du folgende Code­Zeilen ggein­ander austauschst:
Set txDatBez = TextDatumsBezug gegen
Set txDatBez = Intersect(ActiveSheet.UsedRange, TextDatumsBezug) und
Set nuDatBez = ZahlDatumsBezug gegen
Set nuDatBez = Intersect(ActiveSheet.UsedRange, ZahlDatumsBezug)
Dadurch kannst du auch ganze Zeilen oder Spalten als 1.Argument beider UDFs verwenden. Das gilt aber nur für die UDFs, nicht für ältere Xl-Fktt, denen die UDFs als Argument über­geben wdn. Benötigen die, wie im Bsp KKLEINSTE, noch weitere Argumente, sollten diese bereichs­beschränkt ange­geben wdn (SUMMENPRODUKT ist bspw auch so ein Kandidat)!
Ich habe das jetzt auch in den Spalten E:G von BspBlatt2 ange­wendet und gleich­zeitig diesen Bereich auf 10Tsd Zeilen erweitert (Tages­zähler für Daten von 1700 bis 2000). Die MatrixFml in Spalte G benötigt dann zur Berechnung zwar nur ca 35s, aber da Xl mitunter dazu neigt, UDFs öfter als nötig neu zu berechnen (auch, wenn sie wie diese nicht volatil sind), kann es, zumindest bei ungünstigem Blatt­Aufbau und vielen Fmln, schon mal deutlich länger dauern. Insofern ver­stehe ich dein Problem durch­aus, zumal du ja sogar 13Tsd Zeilen hast (Massen-DV mit Xl mit und ohne UDFs kann schon zum Problem wdn, wenn man die schnel­leren Xl-Fktt nicht wirklich dafür ein­setzen kann).
Zu deinen Fragen:
1. Das Datum kann bzw muss als Text eingetragen wdn, wenn du TextToDate benutzen willst, das daraus einen VBA-Tages­zähler macht, der die Grund­lage für alle VBA-Kalender­Fktt bildet.
Bei Datumsangaben ab 1.3.1900 wäre das nicht unbedingt nötig, weil deren Zähler mit den VBA-Zählern über­ein­stimmen, aber du müsstest dann zwischen beiden unter­scheiden, was sich negativ auf auto­mati­sierte Abläufe aus­wirken kann.
2. Einfach subtrahieren kannst du, wenn du nur die Tage ermitteln willst. Bei Jahren ist es schon etwas kom­pli­zierter, da bietet DateDiff schon etwas mehr Komfort, wie du auch meinem obigen Bsp ent­nehmen kannst. Wie man in den ange­gebenen Fällen rechnen muss, ist aus den Fmln ersichtlich. Allerdings musst du auch mit dieser Fkt jeden Teil­wert extra berechnen, also bspw erst die Jahre, dann Monate und Tage, wobei letzteres mit den jewei­ligen Resten erfolgen muss, wenn du ein zusammen­gesetztes Ergebnis der Form JJJJ+MM+TT haben willst (yyyy, m, d). Das Umwandeln der Tages­Differenz mit vbFkt Format (xlFkt TEXT ver­bietet sich ohnehin!) bzw UDF DateToText (die darauf basiert) kann zu leichten Abwei­chungen führen, da hier­bei von einem kon­kreten Datum ausge­gangen wird (s.a. mein entsprd Bsp!), während DateDiff auch die Aus­gangs­Zeit­räume berück­sich­tigt.
3. Meine Sortierung in Blatt2!G:G ist nur ein Bsp für eine Sor­tierung per Fml. DatumsTexte können natürlich auch mit Xl-Routinen (auch per VBA) sortiert wdn, voraus­gesetzt, ihre Form ist ein­heit­lich (zB TT.MM.JJJJ). Ansonsten kannst du natür­lich auch über per UDF erzeugte VBA-Tages­zähler sor­tieren lassen.
4. Evtl ist deine SubE auch suboptimal, will heißen, du solltest in ihr zuerst die auto­matische Berechnung abschalten (ggf auch Ereignis­Reaktion und Bild­schirm­Aktua­lisierung → zum Schluss wieder anschalten!), sofern außerdem (volatile) Fmln u/o Bedingt­Format­Regeln (bzw benannte Fmln) in Blatt (bzw Mappe) ent­halten sind.
Ansonsten solltest du eine solche Datei wohl besser auf manuelle Berechnung einstellen und nur vor dem Speichern generell neu berech­nen lassen. Zwischen­durch dann mit Taste [F9] o.ä.
Abschlussempfehlung:
a) Wofür du dich auch entscheidest, mach's einheitlich und nicht als Xl-VBA-(Kalender-)Mischmasch!
b) Benötigst du weitere Hilfe, von Werner oder Anderen (ich habe für eine umfang­reiche Projekt­Auto­mati­sierung weder Zeit noch Lust), solltest du eine Fort­set­zung(s­Nachfrage) dieses Threads neu ins Forum stellen und dort auf diesen hier mit Link zu seiner Archiv-Adresse ver­weisen. Es kann auch nichts schaden, wenn du hier noch einen Link auf diese Fort­setzung setzt (ebenfalls zu deren Archiv-Adresse!). Für Letzteres hast du von jetzt an maximal 6 Tage Zeit, dann geht das end­gültig nicht mehr!
Morrn, Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige