Autofilter VBA Datum
GK
Ich möchte per VBA den Autofilter für eine Spalte setzen, die ein Datum
enthält.
Folgende VBA-Anweisungen funktionieren nicht und der Filter blendet alle
Datensätze aus.
' rngBereich.AutoFilter posSpalteEinProd.ET, "01.02.04" 'funktioniert
nicht
' rngBereich.AutoFilter posSpalteEinProd.ET, ">01.02.2004"
'funktioniert nicht
' rngBereich.AutoFilter posSpalteEinProd.ET, "=38018" 'funktioniert
nicht
' rngBereich.AutoFilter posSpalteEinProd.ET, "2004/02/01"
'funktioniert nicht
' rngBereich.AutoFilter posSpalteEinProd.ET, ">2004/02/01"
'funktioniert nicht
Schaue ich mir anschließend den gesetzten Filter sowohl in VBA im
Lokalfenster, als auch im Tabellenblatt an, scheint bei allen Versionen die
Schreibweise usw. in Ordnung zu sein.
Bei benutzerdefinierten Filtern z.B. ">01.02.2004" oder in der
amerikanischen Schreibweise ">2004/02/01" kann ich den Dialog
Benutzerdefinierter Filter anzeigen, die Einstellungen sind ok und zeigen den
deutschen Syntax an. Es genügt, die ok-Schaltfläche aufzurufen und der
Filter, der ursprünglich aus VBA gesetzt wurde, funktioniert. Dabei ist die
Schreibweise des Datums egal, auch 2 oder 4-stellige Jahreszahlen machen
keinen Unterschied.
Der Versuch, anstatt des Datums eine serielle Zahl anzugeben führte zu
Teilerfolg: Folgender Code funktionert und ich kann meinen Code entsprechend
anpassen um weiter zu kommen::
rngBereich.AutoFilter posSpalteEinProd.ET, ">38017", xlAnd, "<38019"
'funktioniert
' rngBereich.AutoFilter posSpalteEinProd.ET, ">38018" 'funktioniert
Kein Ergebnis liefert aber:
' rngBereich.AutoFilter posSpalteEinProd.ET, "=38018" 'funktioniert
nicht
Die Datumsspalte enthält wirklich nur glatte Zahlen (in der Formelansicht
kontrolliert!), so dass das Problem, dass das Datum einen Tagesanteil enthält
z.B. 38018,5 eigentlich vernachlässigbar sein sollte.
Hat jemand eine Idee, was im Hintergrund bei der Datumsumwandlung passiert
und wie das Datum in VBA korrekt eingegeben werden muss?
In den Newsgroups fand ich folgenden Hinweis von Herbert Tafener, VBA Datum
in Double z.b CDbl(CDate("01.02.04")) :
Set rngBereich = Range("einProd_SpalteLektor").CurrentRegion
rngBereich.Worksheet.Activate
rngBereich.Worksheet.AutoFilterMode = False
rngBereich.Select
Selection.AutoFilter posSpalteEinProd.ET, "=" & CDbl(CDate("01.02.04")) '
Bei mir hat der Code sowohl unter 2000 als auch 2003 nicht funktioniert. Die
Datumsspalte ist mit tt.MM.JJ (Benutzerdefiniert) formatiert. Auch wenn die
Spalte mit dem entsprechenden "normalen" Datumsformat aus der Auswahlliste
formatiert wird, ergeben sich keine Änderungen im Verhalten.
Stelle ich das Format der Spalte auf Standard funktioniert
CDbl(CDate("01.02.04")) bzw. "=38018"
Vielen Dank für die Hilfe
Gerlinde
Claus Busch
VBA verwendet das US-Format des Datums, also MM/DD/YYYY
z.B. für ein Datum größer 1.1.2006 und kleiner 10.1.2006 in Spalte B:
With ActiveSheet.Range("A1")
.Autofilter Field:=2, _
Criteria1:=">01/01/2006", _
Operator:=xlAnd, _
Criteria2:="<01/10/2006"
End With
--
Mit freundlichen Grüssen
Claus Busch
Win XP Prof SP2; Office 2000 SP3
claus_busch(at)t-online.de
Claus Busch
eine weitere Möglichkeit wäre, deine Datumswerte in der Tabelle zu
hinterlegen, z.B. in G1 und G2. Dann würde es so gehen für Datum in B:
With ActiveSheet.Range("A1")
.Autofilter Field:=2, _
Criteria1:=">" & Range("G1"), _
Operator:=xlAnd, _
Criteria2:="<" & Range("G2")
Claus Busch
Am Sat, 9 Sep 2006 23:57:38 +0200 schrieb Claus Busch:
ist ein Fehler drin. So ist es richtig:
With ActiveSheet.Range("A1")
.Autofilter Field:=2, _
Criteria1:=">" & Range("G1").Value2, _
Operator:=xlAnd, _
Criteria2:="<" & Range("G2").Value2
GK
"Claus Busch" schrieb:
Hallo Claus,
gebe ich das Datum in der Schreibweise "MM/TT/JJJJ" an verhält sich Excel
als würde ich direkt die serielle Zahl eingeben, also etwas besser. Es
funktionieren dann benutzerdefinierte Filter , also <; > usw, aber gleich
(also sozusagen Auswahl aus der AutofilterListe) funktioniert auch mit diesem
Format nicht.
= funktioniert aber mit diesem Format, wenn die Datumsspalte entsprechend
ebenfalls mit MM/TT/JJJJ (benutzerdefiniert) formatiert ist.
Auch wenn das Kriterium ein Verweis auf einen Zelle ist, bleibt das
Verhalten identisch. Interssant ist es allerdings, dass, wenn man sich die
Einstellungen des Autofilters anschließend anschaut, in den Bedingungen steht
: entspricht serielle Zahl.
Ich habe den Verdacht, dass bei = irgendein Konvertierungsfehler vorliegt.
U.U wird bei = ein direkter Stringvergleich durchgeführt, wobei das Format
entscheidend ist. während >< auf die serielle Zahl vergleicht.
Bei Vergleich mit formatierten Werten, wir es wohl Probleme mit den
unterschiedlichen Schreibweisen geben, dafür spricht, dass der Filter
"MM/TT/JJJJ" auch mit = funktioniert, wenn die Spalte entsprechend formatiert
ist.
Ich kann damit leben, mich würde nur interessieren was wirklich los ist.
Danke für die Mühe
Gerlinde
Claus Busch
Am Sun, 10 Sep 2006 03:21:07 -0700 schrieb GK:
> gebe ich das Datum in der Schreibweise "MM/TT/JJJJ" an verhält sich Excel
> als würde ich direkt die serielle Zahl eingeben, also etwas besser. Es
> funktionieren dann benutzerdefinierte Filter , also <; > usw, aber gleich
> (also sozusagen Auswahl aus der AutofilterListe) funktioniert auch mit diesem
> Format nicht.
>
> = funktioniert aber mit diesem Format, wenn die Datumsspalte entsprechend
> ebenfalls mit MM/TT/JJJJ (benutzerdefiniert) formatiert ist.
>
> Auch wenn das Kriterium ein Verweis auf einen Zelle ist, bleibt das
> Verhalten identisch. Interssant ist es allerdings, dass, wenn man sich die
> Einstellungen des Autofilters anschließend anschaut, in den Bedingungen steht
>: entspricht serielle Zahl.
> Ich habe den Verdacht, dass bei = irgendein Konvertierungsfehler vorliegt.
> U.U wird bei = ein direkter Stringvergleich durchgeführt, wobei das Format
> entscheidend ist. während >< auf die serielle Zahl vergleicht.
> Bei Vergleich mit formatierten Werten, wir es wohl Probleme mit den
> unterschiedlichen Schreibweisen geben, dafür spricht, dass der Filter
> "MM/TT/JJJJ" auch mit = funktioniert, wenn die Spalte entsprechend formatiert
> ist.
> Ich kann damit leben, mich würde nur interessieren was wirklich los ist.
ich musste das auch erst testen, da ich noch selten Autofilter auf Datum
per VBA gesetzt habe. Festgestellt habe ich jetzt, dass man einstellige
Tage auch einstellig eingeben muss, also ohne führende 0. Dies hier z.B.
funktioniert auch ohne, dass in der Tabelle umformatiert werden muss für
die Datumswerte 8.1.2006 oder 10.1.2006 in Spalte B:
Sub Autofilter()
With ActiveSheet.Range("A1")
.Autofilter Field:=2, _
Criteria1:="=1/10/2006", _
Operator:=xlOr, _
Criteria2:="1/8/2006"
End With
End Sub
GK
das Hauptproblem ist das =
alles andere funktioniert mit serieller Zahl oder der Schreibweise MM/TT/JJJJ.
Ich würde nicht mehr zuviel Zeit investieren. Es ist ein grundlegendes
Problem aus dem Bereich Datumsformate und gehört meiner Meinung nach in den
Bereich MS Support.
Ich muss fertig werden und werde anstatt =, einfach > Dat1 and < dat2
benutzen und mit seriellen Zahlen arbeiten, dann müsste ich eigentlich
unabhängig von deutschen, englischen usw. Versionen sein.
Vielen Dank für Deine Hilfe falls ich weiter Entdeckungen mache melde ich
mich.
Gerlinde
"Claus Busch" schrieb:
Melanie Breden
GK schrieb:
> Ich möchte per VBA den Autofilter für eine Spalte setzen, die ein Datum
> enthält.
das Problem ist, dass VBA dein Datum nicht als Datum erkennt.
So sollte es gehen:
Dim strDate As String
strDate "10.09.2006"
rngBereich.AutoFilter Field:1, Criteria1:">" & CLng(CDate(strDate))
Mit freundlichen Grüssen
Melanie Breden
--
- Microsoft MVP für Excel -
Microsoft Excel - Die ExpertenTipps http://tinyurl.com/cmned
Das Excel-VBA Codebook http://excel.codebooks.de
Excel-Auftragsprogrammierung
GK
"Melanie Breden" schrieb:
Hallo Melanie,
das Problem ist nicht < oder >, sondern der Vergleich auf =.
Mit < oder > funktioniert Dein Code, aber nicht mit =.
Vielen Dank
Gerlinde
GK
"Claus Busch" schrieb:
Hallo Claus,
Es ist ein Formatproblem! Wenn es funktioniert, müssen VBA und Spalte gleich
formatiert sein. Aber es funktioniert eben nicht!! bei allen!! Formaten.
Ich kann keinen VBA-Code schreiben der Formatabhängig ist, oder ich müsste
das gerade aktuelle Format vor dem Filtern aus der Datumsspalte auslesen. Und
da es offensichtlich nicht mit allen Formaten funktioniert, lasse ich es
lieber sein und bleibe bei meiner pragmatischen Lösung.
Vielen Dank
Gerlinde Kirse
Claus Busch
Am Sun, 10 Sep 2006 04:55:02 -0700 schrieb GK:
> Es ist ein Formatproblem! Wenn es funktioniert, müssen VBA und Spalte gleich
> formatiert sein. Aber es funktioniert eben nicht!! bei allen!! Formaten.
> Ich kann keinen VBA-Code schreiben der Formatabhängig ist, oder ich müsste
> das gerade aktuelle Format vor dem Filtern aus der Datumsspalte auslesen. Und
> da es offensichtlich nicht mit allen Formaten funktioniert, lasse ich es
> lieber sein und bleibe bei meiner pragmatischen Lösung.
ich weiß auch keinen Rat mehr. Bei mir funktioniert es, wenn ich Tabelle
TT.MM.JJJJ formatiert habe und das Datum in VBA M/D/YYYY eingebe. Dann geht
es mit allen Vergleichsoperatoren, auch mit =
Ich hatte nur ein Problem, als ich einstellige Monate und Tage mit
führender 0 eingegeben hatte.
Melanie Breden
GK schrieb:
> das Problem ist nicht < oder >, sondern der Vergleich auf =.
> Mit < oder > funktioniert Dein Code, aber nicht mit =.
dann trickse doch ein bischen:
strDate = "10.09.2006"
rngBereich.AutoFilter Field:=1, Criteria1:=">=" & CLng(CDate(strDate)), _
Operator:=xlAnd, Criteria2:="<=" & CLng(CDate(strDate))
GK
"Claus Busch" schrieb:
Hallo Claus,
eigentlich wollte ich aufgeben:
was funktioniert ist folgendes:
Spalte muss formatiert sein mit dem Standardformat, das mit * gekennzeichnet
ist, dann funktioniert VBA M/T/2004 aber nicht MM/TT/2004.
Hat die Spalte ein anderes Format muss das VBA-Format entsprechend sein.
(z.b. bei einstelligen Werten) Funktioniert aber nicht bei allen Formaten.
Ich denke wir sollten die Diskussion beenden und die Angelegenheit in die
Rubrik Format und Konvertierungsauffälligkeiten verbannen.
Die sauberste Lösung die immer funktioniert ist für mich das Arbeiten mit
der seriellen Zahl und den Operatoren <>, dann habe ich einen numerischen
Vergleich, während bei = offensichtlich immer ein Stringvergleich
durchgeführt wird.
Was passiert wenn die Spalte z.B. mit MMM JJJJ formatiert ist oder mit MMMM
JJJJ. Ich vermute das in diesen Fällen in VBA die amerikanischen Monatsnamen
zum Vergleich benutzt werden müssen z.B June anstatt Juni usw. bzw DEC
anstatt DEZ.
Vielen Dank für Deine Mühe
Gerlinde
>
Claus Busch
> eigentlich wollte ich aufgeben:
> was funktioniert ist folgendes:
> Spalte muss formatiert sein mit dem Standardformat, das mit * gekennzeichnet
> ist, dann funktioniert VBA M/T/2004 aber nicht MM/TT/2004.
> Hat die Spalte ein anderes Format muss das VBA-Format entsprechend sein.
> (z.b. bei einstelligen Werten) Funktioniert aber nicht bei allen Formaten.
> Ich denke wir sollten die Diskussion beenden und die Angelegenheit in die
> Rubrik Format und Konvertierungsauffälligkeiten verbannen.
> Die sauberste Lösung die immer funktioniert ist für mich das Arbeiten mit
> der seriellen Zahl und den Operatoren <>, dann habe ich einen numerischen
> Vergleich, während bei = offensichtlich immer ein Stringvergleich
> durchgeführt wird.
> Was passiert wenn die Spalte z.B. mit MMM JJJJ formatiert ist oder mit MMMM
> JJJJ. Ich vermute das in diesen Fällen in VBA die amerikanischen Monatsnamen
> zum Vergleich benutzt werden müssen z.B June anstatt Juni usw. bzw DEC
> anstatt DEZ.
noch einen letzten Versuch. Formatiere deine Zellen per VBA vor dem
Autofiltern auf das Standarddatum. Dann sollte es immer gehen, egal welches
Format du in den einzelnen Zellen vorher gehabt hast:
Sub Autofilter()
Dim myRange As Range
Dim rngZelle As Range
Dim LRow As Long
'Hier deine Datumsspalte anpassen
LRow = Cells(Rows.Count, 2).End(xlUp).Row
Set myRange = Range("B2:B" & LRow)
'Sieht zwar US-mäßig aus, formatiert aber in das gewählte
'Standardformat
For Each rngZelle In myRange
rngZelle.NumberFormat = "m/d/yyyy"
Next
With Range("A1")
.Autofilter Field:=2, _
Criteria1:=Application.InputBox _
("Bitte ein Datum im Format M/D/YYYY eingeben", _
"Datumseingabe")
End With
End Sub
GK
"Melanie Breden" schrieb:
Hallo Melanie,
damit erreichst Du genau das, was ja funktioniert, nämlich einen numerischen
Vergleich auf die serielle Datumszahl. Tricksen ist nicht das Problem.
Das Problem liegt aber im Stringvergleich bei formatierten Datumsangaben,
bei dem Konvertierungsprobleme auftreten, siehe meine Diskussion mit Claus.
U.U funktioniert Programmcode bei bestimmten Formatierungen, wenn aber aus
irgendwelchen Gründen das Format der Suchspalte geändert wird, liefert der
gleiche Code andere Resultate. Das darf nicht sein.
Gebe ich als Suchkriterium ein Datum ein, muss es in eine serielle Zahl
verwandelt werden und der = Operator muss einen numerischen Vergleich
durchführen, wie es offensichtlich beim Opertor <, >, oder auch => usw
passiert. Stattdessen wird bei = auf String verglichen und es gibt Probleme
mit den diversen Datumsformaten.
Aus meiner ursprünglichen Frage ist eine Grundlagendiskussion geworden.
Vielen Dank für deine Hilfe und Dein Interesse
Gerlinde Kirse
Thomas Ramel
GK schrieb am 09.09.2006
> Ich möchte per VBA den Autofilter für eine Spalte setzen, die ein Datum
> enthält.
> Der Versuch, anstatt des Datums eine serielle Zahl anzugeben führte zu
> Teilerfolg:
> Bei mir hat der Code sowohl unter 2000 als auch 2003 nicht funktioniert.
> Die Datumsspalte ist mit tt.MM.JJ (Benutzerdefiniert) formatiert. Auch
> wenn die Spalte mit dem entsprechenden "normalen" Datumsformat aus der
> Auswahlliste formatiert wird, ergeben sich keine Änderungen im
> Verhalten. Stelle ich das Format der Spalte auf Standard funktioniert
> CDbl(CDate("01.02.04")) bzw. "=38018"
Der Autofilter tut sich generell schwer mit der korrekten Filterung von
Datumswerten; speziell wenn dies aus VBA heraus gesteuert werden soll.
Wie Du bereits richtig festgestellt hast, wir der Format-String verglichen.
Auf diesen Umstand macht auch der folgende KB-Artikel aufmerksam:
http://support.microsoft.com/default.aspx?scid=kb;de;508336&Product=WEXCELGer
Es bleibt offenbar nichts anderes übrig als mit einer Hilfsspalte zu
arbeiten und dort entweder das Datum im Textformat oder als serielle Zahl
auszugeben und darauf dann zu filtern.
Mit freundlichen Grüssen
Thomas Ramel (@Laptop)
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
GK
"Claus Busch" schrieb:
Vielen Dank Claus,
aber ich glaube wir haben das Problem von allen Seiten beleuchtet und
wissen nun worauf bei Autofilter und VBA zu achten ist. Du hast Dir sehr viel
Mühe gegeben, aber im Endeffekt bleibt nur: Wie trickse ich das
Excelverhalten aus. Ich bleibe bei meiner numerischen Lösung <>, das ist mir
die sauberste.
Aber die Diskussion hat mir geholfen zu verstehen was bei = wirklich passiert.
mit freundlichen Grüßen
Gerlinde Kirse
PS. am liebsten würde ich jetzt ausprobieren ob das Problem z.B. auch bei
Spalten auftritt, die mit "Currency" formatiert sind.
Ich konnte es nicht sein lassen:
Ich habe einfach Zahlen 1 bis 6 eingegeben und mit Währung formatiert.
Angezeigt in der Tabelle wird also 4,00 €
Folgende Anweisung fitert die richtigen Daten:
Range("a1").AutoFilter 1, "=4.00 $"
Was soll ich davon halten!! Vielleich liest ja ein MS-Mitarbieter den
Diskussionfaden und macht sich darüber mal Gedanken
Jetzt ist wirklich Schluss.
Gerlinde Kirse
Melanie Breden
GK schrieb:
>> strDate = "10.09.2006"
>>
>> rngBereich.AutoFilter Field:=1, Criteria1:=">=" & CLng(CDate(strDate)), _
>> Operator:=xlAnd, Criteria2:="<=" & CLng(CDate(strDate))
> damit erreichst Du genau das, was ja funktioniert, nämlich einen numerischen
> Vergleich auf die serielle Datumszahl. Tricksen ist nicht das Problem.
> Das Problem liegt aber im Stringvergleich bei formatierten Datumsangaben,
> bei dem Konvertierungsprobleme auftreten, siehe meine Diskussion mit Claus.
Das Problem ist IMO gar kein Problem!
Du willst nach einem Datum filtern, welches als serielle Zahl in der Zelle hinterlegt ist.
Die Problematik beim Vergleichen von Strings in unterschiedlichen Formaten oder gar
Sprachversionen hast du bereits festgestellt.
Ich verstehe nicht, warum du daran festhälst einen String vergleichen zu wollen,
wenn die Filterung über einen Longwert einwandfrei funktioniert.
GK
"Melanie Breden" schrieb:
Melanie,
ich will nicht unbedingt über einen String filtern, sondern mir ist das
Problem absolut klar. Diese Problem tritt übrigends nicht nur bei
datumsformatierten Spalten sondern auch bei währungsformatierten Spalten auf.
Das Problem ist einfach: der Operator = verhält sich anders als >< usw. Wenn
einem das klar ist kann man mit dem Autofilter arbeiten, ansonsten kann man
böse Überraschungen erleben.
Wie ich mein Problem löse ist mir schon seit langem klar,wie ich es schon
mehrfach in meiner Diskussion mit Claus erläutert habe. Ich werde ><
einsetzern und einen numerischen Datumsvergleich auf den seriellen Datumswert
durchführen, nur dann bin ich sicher, dass ich in jeder Sprachversion das
gleiche Ergebnis erziele.
Bei dieser Diskussion geht es nicht mehr um unmittelbare Lösungen sondern
darum, wie verhält sich Excel bei verschiedenen Operatoren. Ich denke das
Problem ist vollständig gelöst.
Vielen Dank
Gerlinde Kirse
Claus Busch
Am Sun, 10 Sep 2006 04:25:02 -0700 schrieb GK:
> Hallo Melanie,
>
> das Problem ist nicht < oder >, sondern der Vergleich auf =.
> Mit < oder > funktioniert Dein Code, aber nicht mit =.
gestern war mal wieder eine Frage mit Autofilter und Datum. Dabei fiel es
mir dann wie Schuppen aus den Haaren. Der Autofilter kennt oder braucht den
Operator = nicht. Entweder kleiner, größer, kleinergleich oder
größergleich. Bei = läßt du einfach den Operator weg. Z.B.
Criteria1:="2/1/2006"