Microsoft Excel

Herbers Excel/VBA-Archiv

Autofilter mit formartierten Zellen mit VBA


Betrifft: Autofilter mit formartierten Zellen mit VBA von: Sandra
Geschrieben am: 14.09.2019 11:59:53

Hallo

Ich habe ein großes Problem bei der Anwendung des Autofilters per VBA bei formatierten Zellen. D. h. das per VBA übergebenen Array filtert keine Datensätze, wenn die Zahl >999 ist, da die Zellformatierung #,##0" ist.

Dasselbe Problem

     
     https://www.herber.de/forum/archiv/1604to1608/1604480_VBA_Autofilter.html

wurde bereits in der Vergangenheit beschrieben, jedoch nur in Kombination mit einem einzelnen Wert und nicht in Verbindung mit einem Array.

Ich habe bisher als Notlösung denselben Weg gewählt wie der User beim obigen Link und dasselbe Problem bei der Zurückformatierung (ausgeblendete Zeilen werden ignoriert), jedoch missfällt mir die Lösung und konnte selbst bei intensiver Suche im www keine Lösung finden. Das zentrale Problem ist, dass bei allen mir bekannten Beispielen, die Zahlen nicht mit dem Tausendertrennzeichen formatiert sind. Will heißen alle machen um diese Formatierung einen extrem großen Bogen.

Gibt es hierzu wirklich keine Lösung? Über eine Antwort mit Lösung würde ich mich dennoch sehr freuen.

Mein bisheriger Lösungsweg (Auszug) lautet:

For each rngB in rngBereich

xkey = rngB.Text 'wenn die Zelle das Format "#,##0" hat (sonst rngB.Value)
dic(xkey) = 0

next rngB

For Each xkey In dic

varSelektion = varSelektion & "," & xkey

Next xkey

varSelektion = Split(Trim(Right$(varSelektion, Len(varSelektion) - 1)), ",")

Die Werte werden mit dem Punkt in das Array eingelesen und mit diesem an die Autofilteranwendung übergeben, jedoch werden diese Werte von Excel nicht erkannt. Dies gilt ebenfalls mit der rngB.Value-Variante, wenn die Zellen das Format "#,##0" haben. Nun bin ich absolut ratlos. Wären die Zellen nicht formatiert, dann funktioniert dies wunderbar, jedoch möchte/muss ich an der Formatierung "#,##0" festhalten.

With ws.Range(strStartspalteallgemein & bytStartzeile - 1 & ":" & strSpalte & iRow)

.AutoFilter
.AutoFilter Field:=intAutofilterfeld, Criteria1:=Array(varSelektion), Operator:=xlFilterValues

End With
  

Betrifft: AW: Autofilter mit formartierten Zellen mit VBA von: 1713052.html
Geschrieben am: 14.09.2019 13:11:41

Poste besser deine Datei statt irgend einen alten Fall, wo zudem der Link so nicht funktioniert, weil du ihn als "Code" gepostet hast.

  

Betrifft: AW: Autofilter mit formartierten Zellen mit VBA von: 1713053.html
Geschrieben am: 14.09.2019 13:47:49

Hi

wenn du beim Autofilter einen Vergleichsfilter verwendest (<>=), muss die nachfolgende Zahl unabhängig vom Zahlenformat der Zellen immer im amerikanischen Format (Punkt als Dezimalzeichen falls erforderlich) und ohne Tausendertrennzeichen angegeben werden:

Criteria1=:">=1000.1"
beim Filtern über das FilterArray muss die Zahl genauso geschrieben werden, wie in der Zelle angezeigt wird.


zu deinen Fehlern:
du verwendest zum Erstellen des Arrays über einen String das KOMMA als Trennzeichen.
das funktioniert aber nicht, weil deine Zellwerte ja selbst das Komma enthalten, damit bekommst du zusätzliche Trennstellen. Verwende ein Trennzeichen, welches in den Werten nicht vorkommen kann, bspw "|".
die ganze Aktion ist aber nicht notwendig.
im Prinzip reicht um die Schlüsselwörter des Dictionarys in ein eindimensionales Array zu schreiben:
varSelektion = dic.Keys

wenn du varSelektion schon in ein Array ist, reicht:
Criteria1:=varSelection
kleiner Tip am Rande:
das Löschen des Trennzeichens am Anfang geht am einfachsten MID(Text, 2)


Gruß Daniel
  

Betrifft: AW: Autofilter mit formartierten Zellen mit VBA von: 1713091.html
Geschrieben am: 14.09.2019 18:26:40

Hallo Daniel

Erst einmal vielen Dank für Ihre Antwort und die damit verbundenen Erklärungen.

Das mit dem Komma habe ich deshalb gewählt, weil dieses Zeichen in den Spalten nicht als Zeichen vorkommen kann, da definitiv nur rationale Zahlen vorkommen können. Aber trotzdem danke ich für diesen Tipp mit dem dic.keys, da ich diese Variante nicht kannte und ein gewöhnliches Array nicht als ein solches beim Autofilter erkannt wurde. Daher dieser Umweg, jedoch hatte das Trennzeichnen Komma keinen negativen Einfluss auf das Ergebnis, da zuvor die Splitfunktion angwwendet wurde.

Aber dennoch hat sich am Ergebnis nichts verändert. D. h. es werden noch immer die Zahlen größer 999 nicht erkannt (der gesamte Bereich (hier: Spalte) ist definitiv mit "#,##0" formatiert), obwohl im dic der Zelltext(.text) eingelesen wird und nicht der Zellvalue (.value).

Daher bin ich mit meinem Latein am Ende, obwohl das Problem an und für sich nicht wirklich groß ist. Aber manchmal sind es gerade die winzig kleinen Probleme, die alles hochkompliziert machen.

Für weitere Lösungsansätze bin ich daher weiter offen.

Viele Grüsse

Sandra

  

Betrifft: AW: Autofilter mit formartierten Zellen mit VBA von: 1713112.html
Geschrieben am: 14.09.2019 19:24:33

Hi
lade bitte die Datei hoch.
gebe bitte an, welche Länderversion du verwendest.

btw:
"Das mit dem Komma habe ich deshalb gewählt, weil dieses Zeichen in den Spalten nicht als Zeichen vorkommen kann"
"(der gesamte Bereich (hier: Spalte) ist definitiv mit "#,##0" formatiert)"
Entschuldige meine Frage, aber wie würdest du das Zeichen bezeichnen, das zwischen dem ersten und zweiten "#" steht? für mich sieht das aus wie ein Komma, und wenn du die Zellen mit diesem Zahlenformat formatiert hast, dann enthalten die Zellewerte mit Zahlen auch ein Kommam, wenn du sie mit .Text ausliest.

Gruß Daniel

  

Betrifft: AW: Autofilter mit formartierten Zellen mit VBA von: 1713128.html
Geschrieben am: 14.09.2019 21:07:49

Hallo Daniel

Vielen Dank für Ihre Antwort.

Ich meine, dass wir etwas einander vorbeigeschrieben haben, weil ich Ihre Ausführungen in Bezug auf die nicht erforderliche Erstellung eines Datenstrings mit anschließender Splittung über das Komma anders interpretiert habe als von Ihnen beabsichtigt. Nur wundere ich mich dennoch, dass hier die lokale Variante maßgebend ist (keine spezielle Einstellung vorgenommen) und im Hintergrund nicht automatisch mit dem amerikanischen Format gerechnet wird (vgl. Prinzip beim Datum als Longwert).

Nun habe ich aber Ihren Kommentar verstanden und die Zellwerte mit der Formatierung "###0" an das dic übergeben. Auf diese Weise funktioniert die Datenfilterung über VBA nun sehr gut.

Nochmals herzlichen Dank für Ihre beiden Kommentare und den damit verbundenen Hilfestellungen.

Viele Grüße

Sandra

Beiträge aus dem Excel-Forum zum Thema "Autofilter mit formartierten Zellen mit VBA"