MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 15.05.2018, 12:12   #1
teflondon
MOF User
MOF User
Standard VBA - VBA Abfrage findet Daten, die nicht existieren??

Hi zusammen!

In der angefügten Beispieldatei befindet sich der VBA Code:

Code:

Option Explicit

'FARBABFRAGE ALTE HSL TABELLE PRÜFSTÄNDE
'Grünvariationen = Grün, Rosa = Weiß


Public Function Pstatus(igSuchbereich As String)
Dim igzelle As Range
Dim igBereich As Range

Dim igGruen As Boolean, igRot As Boolean, igGelb As Boolean, igWeiss1 As Boolean, igWeiss As Boolean, igdunkelgruen As Boolean, iggruen2 As Boolean, iggruen3 As Boolean, igRosa As Boolean

Application.Volatile


Set igBereich = Range(igSuchbereich)
For Each igzelle In igBereich
    If igzelle.Interior.ColorIndex = 4 Then
        igGruen = True
    Else
        igGruen = False
    End If
    If igzelle.Interior.ColorIndex = -4142 Then igWeiss1 = True
    If igzelle.Interior.ColorIndex = 2 Then igWeiss = True
    If igzelle.Interior.ColorIndex = 3 Then igRot = True
    If igzelle.Interior.ColorIndex = 6 Then igGelb = True
    If igzelle.Interior.ColorIndex = 43 Then igdunkelgruen = True
    If igzelle.Interior.ColorIndex = 14 Then iggruen2 = True
    If igzelle.Interior.ColorIndex = 10 Then iggruen3 = True
    If igzelle.Interior.ColorIndex = 38 Then igRosa = True
    
    
   
    
    
Next igzelle

Pstatus = "N.V. IN LISTE"
If igWeiss1 Then Pstatus = "ANGELEGT"
If igWeiss Then Pstatus = "ANGELEGT"
If igRosa Then Pstatus = "ANGELEGT"
If igGruen Then Pstatus = "GEHT"
If igdunkelgruen Then Pstatus = "GEHT"
If iggruen2 Then Pstatus = "GEHT"
If iggruen3 Then Pstatus = "GEHT"
If igGelb Then Pstatus = "GEHT Z.T."
If igRot Then Pstatus = "GEHT NICHT"



End Function
Liefert mir anhand der Zellfärbung bestimmte Status. Mit der Abfrage "=pstatus("STANDARD!E"&VERGLEICH(WERT($C2);STANDARD!$A:$A))" wird die Position der Zelle ermittelt.

Diese Function funktionierte soweit eigentlich immer. Seit kurzem liefert er mir jedoch Werte bzw. Ergebnisse für Suchwerte, die garnicht in der Liste vorkommen?? (siehe Reiter Tabelle2, C2). Die Soll Werte sind anbei ergänzt.

Sehe im Code auch nicht, woran das liegen soll

Logik müsste soweit passen oder?

Vielen Dank schon mal
Angehängte Dateien
Dateityp: xlsm 201805115_BSP.xlsm (157,0 KB, 7x aufgerufen)
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 12:20   #2
Klaus-Dieter
MOF Koryphäe
MOF Koryphäe
Standard

Hallo,

Auswertungen nach Zellformaten sollte man nicht durchführen. Besser ist es, nach den Bedingungen zu arbeiten, die zum Zellformat geführt haben.

__________________


Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Klaus-Dieter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 12:30   #3
EarlFred
MOF Guru
MOF Guru
Standard

ein Klassiker! Vergleich ohne 3. Argument prüft NICHT auf genaue Übereinstimmung! Das 3. Argument muss 0 sein.

Zu der UDF schreibe ich lieber nichts. Nimm Dir dringend Klaus-Dieters Rat zu Herzen und arbeite lieber mit einer "harten" Kennung anstelle mit Farben.

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 6 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,044% per 26.07.2018) - eine tolle Geste!

Geändert von EarlFred (15.05.2018 um 12:33 Uhr).
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 14:29   #4
teflondon
Threadstarter Threadstarter
MOF User
MOF User
Standard

Vielen Dank für den Hinweis,

leider ist dies schier unmöglich. Die Farben symbolisieren Status von Freigaben und sind ein Export einer Software...Inhalt dieser Zellen sind stets Datum oder vielseitige Kommentare.

Ich muss also über die Färbung gehen. Die Frage ist zusätzlich auch, ob ich dies über Color.Index mache oder über die RGB Werte...Color. Index soll ja manchmal variieren trotz gleicher Farbe

Wie gesagt, ist seltsam, dass es bei 1400 Abfragen immer funktionierte und nun auf einmal bei nicht Finden des Parameters das Ergebnis für weißen Inhalt liefert...


Kann die zwei Cases ja auch einfach tauschen, aber möchte wissen, wieso das genau auf einmal passiert
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 15:02   #5
Klaus-Dieter
MOF Koryphäe
MOF Koryphäe
Standard

Hallo,

weder noch, nimm die Farbwerte mit denen die neuen Excelversionen arbeiten.

__________________


Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Klaus-Dieter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 15:10   #6
EarlFred
MOF Guru
MOF Guru
Standard

Zitat:

Wie gesagt, ist seltsam, dass es bei 1400 Abfragen immer funktionierte und nun auf einmal bei nicht Finden des Parameters das Ergebnis für weißen Inhalt liefert...
[...]möchte wissen, wieso das genau auf einmal passiert

Nochmal mein Hinweis aus #3: Der Fehler liegt in der Vergleich-Funktion!

Code:

=pstatus("STANDARD!E"&VERGLEICH(WERT($C2);STANDARD!$A:$A;0))
Ohne das 3. Argument besteht die Chance, dass ein anderer Wert als der gesuchte zurückgegeben wird (lies bitte die OH zu der Funktion). Mit diesem arbeitet dann Deine VBA-Function - die Eingangsgröße ist also falsch, da muss das Ergebnis dann auch nicht zwangsläufig richtig sein.

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 6 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,044% per 26.07.2018) - eine tolle Geste!
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 20:35   #7
teflondon
Threadstarter Threadstarter
MOF User
MOF User
Standard

Ja, mir ist die Formel schon geläufig, nur seltsam, dass bei der Datenmenge noch nie was falsch war und nun schlagartig Fehler auftreten.

Mit dem 3 Argument wird mir sowieso nur ein "WERT" statt des "N.V. IN LISTE" geliefert, was die Passage im Code ja dann irgendwie überflüssig macht, bzw. mir keine Infos darüber liefert, ob die Daten in den Listen vorhanden sind.

Über WENNFEHLER krieg ich ja dann auch nur raus, ob ein Fehler vorliegt, aber nicht woher er rührt.

Muss öfter die Datenbasis in WERT(Glätten) umwandeln...evtl. da ein Fehler?

Der Code ist also richtig soweit?

@Klaus-Dieter:

Wie ließe sich das mit den Farben realisieren? Kann ich den bestehenden Code von der Logik so übernehmen und nur den Befehl Color. Index und die Werte für die Farben anpassen?

Geändert von teflondon (15.05.2018 um 20:37 Uhr). Grund: vergessen
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 21:08   #8
EarlFred
MOF Guru
MOF Guru
Standard

„Der Code ist also richtig soweit?“
Da du etwas anderes als Ergebnis erwartest, kennst du doch die Antwort.

Pseudocode:
=pstatus(Wennfehler(„Adresse“&Vergleich(...;0);“Fehler“))

In der Function dann zuerst prüfen, ob der Wert „Fehler“ übergeben wurde und entsprechend reagieren.

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 6 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,044% per 26.07.2018) - eine tolle Geste!
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 21:18   #9
Scorefun
MOF Meister
MOF Meister
Standard

Zitat: von teflondon Beitrag anzeigen

Ja, mir ist die Formel schon geläufig
...

Der Code ist also richtig soweit?


Herrliche Antwort...

__________________

Gruss Ralf
======================================
Bitte keine PN Anfragen - Hilfe erfolgt nur im Forum!
======================================
Vorsicht Beim Ausprobieren...
Auch Chaotischer Code Entwickelt Sich Ständig
1) Intel I5-2500K, 4x3,3 Ghz, 8GB RAM, Asus P8P67, 250GB SSD Toshiba Q-Pro, 1,5TB HD, MSI GTX550TI, Win7-Prof 64-bit SP1, Office 2010 Plus SP1
2) Asus Zenbook Flip UX360UAK-C4203T, 13,3 Zoll, i5-7200U, 2,5 Ghz, 8GB RAM, 256GB SSD, Win10 Home 64-bit, Office 2016 Plus
Scorefun ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 06:26   #10
teflondon
Threadstarter Threadstarter
MOF User
MOF User
Standard

@EarlFred:

Ja erwarte was anderes, aber keine Ahnung obs am Code selbst oder an der Einbettung in der Formel liegt.

@Scorefun:

Wo genau liegt da die Komik?
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 08:08   #11
EarlFred
MOF Guru
MOF Guru
Standard

Zitat:

aber keine Ahnung obs am Code selbst oder an der Einbettung in der Formel liegt

Denn geh doch systematisch vor:

Es beginnt bei der Formel, also betrachte diese zuerst - und zwar den innenliegenden Teil, der zuerst berechnet wird. Diesen korrigierst Du wie besprochen zu:
Code:

=VERGLEICH(WERT($C2);STANDARD!$A:$A;0)
2 Varianten (strenggenommen mehr):
a) Es gibt eine Übereinstimmung - die Formel liefert eine Zahl. Dieser Fall sollte keine Probleme bereiten
b) Es gibt KEINE Übereinstimmung - die Formel liefert einen Fehlerwert, genauer #NV.
(c) #Wert!, wenn die Wandlung mit der Funktion Wert() fehlschlägt. Ich erkenne den Sinn der Funktion Wert() hier aber nicht, da ohnehin Zahlenwerte vorliegen. Zahlen müssen nicht in Zahlen gewandelt werden. Im folgenden lasse ich diese Funktion also weg. Wenn Du sie - aus welchen Gründen auch immer, brauchst, bau sie wieder ein.)

Im nächsten Schritt bildest Du einen verketteten Text:
"STANDARD!E"&VERGLEICH($C2;STANDARD!$A:$A;0)
Auch dieser Ausdruck liefert wegen des Fehlers #NV.

Du übergibst dann den Fehlerwert #NV an die UDF pstatus - und was erwartet diese?
Code:

Public Function Pstatus(igSuchbereich As String)
Einen Fehlerwert kann diese Function also nicht verarbeiten, weil Du das per Deklaration ausgeschlossen hast. Ergebnis ist der Fehler #Wert!.

Du musst also als eine Möglichkeit, wie ich zuletzt schrieb (hast Du meinen Tipp denn überhaupt mal ausprobiert?), einen String an die Function übergeben, den diese dann auch bearbeiten kann. Das ist aber auch recht unsauber, dieses ganze String-Gemauschel.
Also musst Du doch an Deine Function, was ich eigentlich vermeiden wollte.

Ändere den Aufruf:
Code:

=pstatus(INDEX(STANDARD!E:E;VERGLEICH($C2;STANDARD!$A:$A;0);))
und passe die Function an:
Code:

Option Explicit

'FARBABFRAGE ALTE HSL TABELLE PRÜFSTÄNDE
'Grünvariationen = Grün, Rosa = Weiß

Public Function Pstatus(vBereich As Variant) As Variant

'''''''Application.Volatile  'wozu?

Select Case LCase$(TypeName(vBereich))
  Case "range"
'''''''For Each igzelle In igBereich 'wozu die Schleife, wenn nur eine einzelne Zelle übergeben wird?
    Select Case vBereich.Interior.ColorIndex
      Case -4142, 2, 38
        Pstatus = "ANGELEGT"
      Case 4, 10, 14, 43
        Pstatus = "GEHT"
      Case 6
        Pstatus = "GEHT Z.T."
      Case 3
        Pstatus = "GEHT NICHT"
      Case Else
        Pstatus = "Farbauswertung nicht definiert!"
    End Select
'''''''Next igzelle
  Case "error"
    Select Case vBereich
      Case CVErr(xlErrNA)
        Pstatus = "N.V. IN LISTE"
      Case Else
        Pstatus = vBereich
    End Select
End Select

End Function
Grüße
EarlFred

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 6 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,044% per 26.07.2018) - eine tolle Geste!

Geändert von EarlFred (16.05.2018 um 08:26 Uhr).
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 10:21   #12
teflondon
Threadstarter Threadstarter
MOF User
MOF User
Standard

Hey, vielen Dank!!

Hab den Code nicht selbst erstellt, sondern auch u.a. hier aus dem Forum. Habe selbst kaum Ahnung von der Programmier Materie, bitte um Nachsicht

Der Code funktioniert soweit sehr gut. Das mit der Schleife hat den Hintergrund, dass ursprünglich immer eine Range von 3 Zellen abgefragt wurde, und je nach Vorkommnis einer Farbe, ein Status entstand, siehe Reiter "logik" in der Datei.

Mit =pstatus(A13:C13) z.b. kommt nur bei gleicher Farbe aller Zellen ein Ergebnis "GEHT NICHT". Sobald unterschiedliche Farben in der Range sind, kommt "Farbauswertung nicht definiert".

Liegts am Code selbst oder muss ich die Range in der Formel nur anders definieren?
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 10:22   #13
teflondon
Threadstarter Threadstarter
MOF User
MOF User
Standard

Hier noch die Beispieldatei
Angehängte Dateien
Dateityp: xlsm 201805115_BSP.xlsm (159,3 KB, 0x aufgerufen)
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 10:25   #14
EarlFred
MOF Guru
MOF Guru
Standard

Zitat:

Hab den Code nicht selbst erstellt [...] Habe selbst kaum Ahnung von der Programmier Materie

sowas piept mich an: Du kommst mit einem Code angelatscht, tust so, als sei es deiner, und dann hast du ihn nichtmal kapiert! Sag doch gleich, dass Du Null Ahnung von der Materie hast, dann kann man die Hilfe darauf abstellen und spart sich zig Fehlversuche!

Und jetzt das:

Zitat:

Mit =pstatus(A13:C13) z.b. kommt nur bei gleicher Farbe aller Zellen ein Ergebnis "GEHT NICHT"

Junge! Das ist eine komplett andere Anforderung! Warum sollte da das gleiche oder gar richtige rauskommen? Sag doch gleich, dass Du was anderes willst!

von der ursprünglichen Frage

Zitat:

Abfrage findet Daten, die nicht existieren??

sind wir meilenweit entfernt! Und es kommt immer noch was nachgeschoben

Willst Du uns verkaspern?

Ende aus
/ignore

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 6 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,044% per 26.07.2018) - eine tolle Geste!

Geändert von EarlFred (16.05.2018 um 10:31 Uhr).
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 10:41   #15
teflondon
Threadstarter Threadstarter
MOF User
MOF User
Standard

Hmm,

mein Problem war ja nicht die Range, sondern das Finden von Daten, die nicht existieren...und das auch nur seit kurzem. Der Code funktionierte ja.

Die Sache mit der Range habe ich vergessen, das stimmt, sorry.


Wenn eine Anpassung mit Möglichkeit einer Rangeabfrage viel Aufwand ist, dann kann ich die Haltung nachvollziehen...kann/ konnte dies ja nicht einschätzen.

Falls dem so ist, versuche ich mit dem nun geposteten Code selbst was zu basteln, dafür vielen Dank
teflondon ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 12:18 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.