Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA - anstatt suche in Zelle, Array auslesen

VBA - anstatt suche in Zelle, Array auslesen
22.09.2017 08:15:57
Marco
Guten morgen ihr Lieben ich benötige eure Unterstützung.
Ich habe eine Auswertung, bei der neun Materialnummern aus neun Position im Arbeitsblatt herangezogen (Code siehe unten aus Modul 2) werden. Die erste Materialnummer steht im Dashboard in G9. Mein Problem ist das ich jetzt mehr als 9 Materialnummern habe die für die Auswertung relevant sein können. Das heißt das in der Zelle G9 nicht mehr nur nach einer Materialnummer abgefragt werden soll sondern nach 2 - 3 Nummern. Ich kann zwar in die Zelle G9 mehrere Materialnummern eingeben, aber mein Code diverziert dann nicht ob er nach den einzelnen Nummern suchen soll die in der Zelle stehen. Daher dachte ich das man anstatt die Materialnummern in Zellen abzufragen 9 Arrays abfragt in denen die jeweiligen Materialnummern stehen die ich vorher bestimmt habe. Jetzt weiß ich erstens nicht, ob meine Idee die richtige ist oder es auch anders geht und zweitens fehlt mir das Talent für die Umsetzung, wenn es gehen sollte. Daher bitte ich euch mir zu helfen.
Set rFind = DSB.Range("G9", DBEdr).Find(What:=Material, After:=Range("G9"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Die Beispieldatei habe ich mit angehängt:
https://www.herber.de/bbs/user/116440.xlsm

30
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
22.09.2017 17:16:10
onur
Warum so kompliziert?
dim v,xx
v = Split(Cells(9, 7), ";")
For xx = 0 To UBound(v)
If Val(v(xx)) = Material Then
'Dein Code
End If
Next xx
rFind.Column und rFind.Row brauchst du doch auch nicht, die sind doch eh´ 7 und 9.
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 18:28:00
Marco
hi onur danke für den tipp aber wie und wo soll ich deinen Code einbauen und was genau bewirkt der? kann ich damit mehrere materialnummern in einer zelle abfragen?
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 19:47:43
onur
In dem du die durch semikolon getrennten Mat.-Nr splittest und in ein aray packstz und in der Schleife (for xx=...) das array absuchst, ob =Material.
Also mein Code stattStatt dem hier:
Set rFind = DSB.Range("G9", DBEdr).Find(What:=Material, After:=Range("G9"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If rFind Is Nothing Then MsgBox "Suchlauf Fehler" & vbLf & Station & " " & Material & " - nicht gefunden"
If Not rFind Is Nothing Then
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 20:54:19
Marco
Danke das du mir nochmal geschrieben hast Onur, ich habe meinen Code durch deinen ersetzt aber ich weiß nicht wo ich das "End If" und "Next xx" hinsetzen soll. Also ich habe es jetzt an mehreren Stellen ausprobiert, aber es funktioniert leider noch nicht. Kannst du mir bitte helfen ich würde nämlich gerne deinen Code ausprobieren.
VG Marco
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 21:17:55
onur

For k = 5 To lzAsw
z = 0  'Station Zeile löschen
Station = .Cells(k, 3).Value
Material = .Cells(k, 4).Value
v = Split(Cells(9, 7), ";")
found = False
For xx = 0 To UBound(v)
If Val(v(xx)) = Material Then
found = True
Exit For
End If
Next xx
If Not found Then
MsgBox "Suchlauf Fehler" & vbLf & Station & "  " & Material & " - nicht gefunden" _
Else
'Suche die Material Nummer in Dashboard Spalte "G9" - XXX
'Set rFind = DSB.Range("G9", DBEdr).Find(What:=Material, After:=Range("G9"),  _
LookIn:=xlValues, _
'LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,  _
MatchCase:=False)
'If rFind Is Nothing Then MsgBox "Suchlauf Fehler" & vbLf & Station & "  " &  _
Material & " - nicht gefunden"
'If Not rFind Is Nothing Then
'Bereichs Name zum Prüfen laden
Bereich = DSB.Cells(9, 4).Value
dsbcol = 7 + 1  'Material Spalte
'wenn vorhanden suche Station Name in diesem Bereich
'** Aussprung wenn next Bereich vorkommt!!
end if

Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 21:22:01
onur
Nix für ungut, aber dein Code ist so kompliziert geschrieben und enthält so viel überflüssigen Müll, dass man, wenn ich wüsste, wofür das Ganze gut ist und was es genau machen soll, alles auf vieleicht 20-30% des Codeaufwandes reduzieren könnte.
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 22:19:30
Marco
Kein Ding, aber ich bin froh das der Code funktioniert. Ich kann dir schon sagen was der Code macht. In der Datenbank werden Fehldercodes aufgelistet mit Ausfalldatum, mit Materialnummer, dann an welcher Anlage der Fehler war und welche Baugruppe davon betroffen ist. Wenn ich jetzt den Button drücke sollen die drei häufigsten Fehler die an der jeweiligen Anlage aufgetreten sind in Abhängigkeit von dem Zeitraum den ich gewählt habe und der Materialnummer die z.B. Siemensgeräte symbolisiert angezeigt werden.
Also angenommen an der Anlage 20002 hat es 20 ausfälle mit der Materialnummer 210 gegeben. Dann werden die drei häufigsten Fehlerursachen absteigend aufgelistet.
Wenn ich deinen Codeteil kopiere und es teste erhalte ich die Meldung "Fehler beim kompilieren: Erwartet: Datenfeld"
Grüße Marco
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 22:23:52
onur
In welcher Zeile denn?
AW: VBA - anstatt suche in Zelle, Array auslesen
23.09.2017 22:24:23
onur

Option Explicit      '29.8.2017     'überarbeitet 13.9.  Piet  Herber Forum
'überarbeitet:  29/30.8.
'Datum an Fehlertext angehangen
Dim Bereich As String, rFind As Object
Dim Station As String, dsbcol As Integer
Dim DBEdr As String, Material As Variant
Dim lzAsw As Long, lzDsb As Long, z As Long
Dim DTxt As String, Jahr As Variant
Dim found As Boolean
Dim v, xx
'Modul zum Dashboard ausfüllen
Sub Dashboard_auflisten()
Dim j As Integer, k As Integer, ü As Integer
Dim Cache As Worksheet, DSB As Worksheet
Set Cache = Worksheets("Cache")
Set DSB = Worksheets("Dashboard")
'**********  Dashboard Programm  **********
'alte Dashboard Werte löschen + Überlauf
DSB.Range("H11:I40, V11:W40, AJ11:AK40, H49:I117, V49:W117, AJ49:AK117, H126:I170, V126:W170, _
AJ126:AK170").ClearContents
Cache.Range("G5:H500") = Empty   '** zum testen gelöscht
'"Überlauf" Innenfarbe löschen
'**  Spalten J-S sind ausgeblendet !!
DSB.Columns("J:T").Interior.ColorIndex = xlNone
DSB.Columns("X:AH").Interior.ColorIndex = xlNone
DSB.Columns("AL:AV").Interior.ColorIndex = xlNone
Application.ScreenUpdating = True
'Dashboard End-Adresse feststellen  (für Suchlauf)
DBEdr = DSB.Cells.SpecialCells(xlCellTypeLastCell).Address
lzDsb = DSB.Range(DBEdr).Row + 1  'LastZell in Dashboard
With Cache
'LastCell in Auswertung ermitteln Spalte F
lzAsw = .Cells(Rows.Count, 6).End(xlUp).Row
Jahr = Year(DSB.Range("E7"))   'akt. Jahr
'Schleife für alle Stationen aus Cache
'** For k = 4 To lzAsw
For k = 5 To lzAsw
z = 0  'Station Zeile löschen
Station = .Cells(k, 3).Value
Material = .Cells(k, 4).Value
v = Split(Cells(9, 7), ";")
found = False
For xx = 0 To UBound(v)
If Val(v(xx)) = Material Then
found = True
Exit For
End If
Next xx
If Not found Then
MsgBox "Suchlauf Fehler" & vbLf & Station & "  " & Material & " - nicht gefunden" _
Else
'Suche die Material Nummer in Dashboard Spalte "G9" - XXX
'Set rFind = DSB.Range("G9", DBEdr).Find(What:=Material, After:=Range("G9"),  _
LookIn:=xlValues, _
'LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,  _
MatchCase:=False)
'If rFind Is Nothing Then MsgBox "Suchlauf Fehler" & vbLf & Station & "  " &  _
Material & " - nicht gefunden"
'If Not rFind Is Nothing Then
'Bereichs Name zum Prüfen laden
Bereich = DSB.Cells(9, 4).Value
dsbcol = 7 + 1  'Material Spalte
'wenn vorhanden suche Station Name in diesem Bereich
'** Aussprung wenn next Bereich vorkommt!!
For j = 9 + 1 To lzDsb
If Left(DSB.Cells(j, "D"), 7) = "Bereich" Then Exit For
If DSB.Cells(j, "D") = Station Or DSB.Cells(j, "E") = Station Then z = j:  _
Exit For
Next j
If z = 0 Then MsgBox Bereich & ":  " & Station & "  nicht gefunden"
'wenn vorhanden Top 3 Fehler auflisten
If z > 0 And DSB.Cells(j, dsbcol) = Empty Then
'Schleife für Top 3 Fehler auflisten
'Aussprung bei  Station oder Material
For j = 1 To 3
'Datum Text laden und verkürzen  (ohne Jahr + sec.)
With .Cells(k, 2)
If IsDate(.Value) Then
DTxt = Format(CDate(.Value), "DD.MM. hh.mm")
Else
DTxt = .Text
End If
End With
If .Cells(k, 3)  Station Then Exit For   'Station
If .Cells(k, 4)  Material Then Exit For  'Material
DSB.Cells(z, dsbcol + 0) = .Cells(k, 6)    'Häufigkeit
DSB.Cells(z, dsbcol + 1) = .Cells(k, 5)  'Old, ohne Zeit
.Cells(k, 7).Value = " Ok"                 'ist notiert
z = z + 1:  k = k + 1  'next Zeile
Next j
'**  Offset dsbcol + 12 weil Spalten J-S ausgeblendet sind !!
If .Cells(k, 3) = Station And .Cells(k, 4) = Material Then
DSB.Cells(z - 1, dsbcol + 2).Interior.ColorIndex = 3   'J,X,AL
DSB.Cells(z - 1, dsbcol + 12).Interior.ColorIndex = 3  'T,AH,AV
'Schleife für Überflüssige Fehler überspringen
For j = 1 To 20
.Cells(k, 8).Value = " Übl"                 'ist notiert
k = k + 1  'next Zeile
If .Cells(k, 6) = Empty Then Exit For   'Häufigkeit
If .Cells(k, 3)  Station Then Exit For  'Station
If .Cells(k, 4)  Material Then Exit For 'Material
Next j
End If
k = k - 1   'Korrektur -1 für next Startadresse
ElseIf z > 0 Then
MsgBox " Zelle bereits belegt:  " & DSB.Cells(j, dsbcol).Address
End If
End If
Next k
'LastCell für "Überlauf" ermitteln Spalte G
lzAsw = .Cells(Rows.Count, 7).End(xlUp).Row
For k = 5 To lzAsw
If .Cells(k, 7) = Empty Then ü = ü + 1
If .Cells(k, 7) & .Cells(k, 1) = Empty Then
.Cells(k, 1) = "Überlauf"
.Cells(k, 1).Font.ColorIndex = 3
End If
Next k
End With
'Überlauf in Dahsboard notieren
'If ü = 1 Then DSB.Range("I8") = ü & "  Überlauf"
'If ü > 1 Then DSB.Range("I8") = ü & "  Überläufe"
Application.ScreenUpdating = True
'If ü = 1 Then MsgBox ü & "  Überlauf"
'If ü > 1 Then MsgBox ü & "  Überläufe"
End Sub

Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 04:31:15
onur
Wird das Blatt "Cache" überhaupt benötigt oder ist das nur ein Blatt für irgendwelche Zwischenberechnungen?
Wieso nimmst du nicht einfach ein Paar Spalten mehr statt 2-3 Materialien in die selbe Spalte zu nehmen?
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 07:54:10
marco
Morgen onur,
ich habe den Code getestet aber jetzt berechnet er die Fehler nicht mehr richtig. Die Cache Seite benötige ich schon da werden die Daten zwischen gespeichert, gefiltert, berechnet und sortiert.
Meinst du mit Spalten Zellen? Mir wäre es sogar lieber für jede Materialnummer nur eine Zelle zu verwenden aber ich möchte dann die Materialnummern am liebsten in einem anderen Blatt speichern und dort abfragen und nicht alle auf der Dashboardseite haben. Geht das denn überhaupt?
Grüße Marco
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 11:42:31
Marco
Jetzt weiß ich was du meinst Onur, ich habe das ganz anders verstanden. Also ohne diese Seite sieht es übersichtlicher aus, aber ich habe das Blatt da umzusehen ob der Code funktioniert und die Auswertung richtig ist.
Ich sehe gerade du hast meinen Code gelöscht und alles komplett neu geschrieben. Deswegen wird wohl auch mein Überlauf (wenn es mehr als drei Fehlertypen gibt wird am rand etwas rot markiert) nicht mehr angezeigt. Also die rote Markierung ist weg. Dein Code zeigt die Top 3 Fehler im ersten Bereich nicht richtig an. Aber das mehrere Materialnummern jetzt in einer Zelle erkannt werden funktioniert jetzt.
Gruß Marco
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 11:49:43
onur
" Dein Code zeigt die Top 3 Fehler im ersten Bereich nicht richtig an" - Was genau ist falsch?
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 11:53:40
onur
Wenn ich mir deine Datei anschaue und meine Datei - Dier zeigen beide die gleichen Fehler an.
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 12:02:17
Marco
Sorry stimmt. Ich habe meine Datei nochmal gestartet und du hast recht. Wenn ich den Zeitraum 07.08.2017 bis 26.08.2018 wähle also ersten bis letzten Eintrag ist die Anzahl gleich. Einziger unterschied ist das bei mir die Überläufe angezeigt werden und bei dir nicht mehr und das im ersten Bereich in V23 bis V25 deine Fehler aufsteigend und nicht absteigend angezeigt werden.
Du hast gesagt das es besser wäre die Materialnummern nicht alle in eine Zelle zu speichern sondern vll in einem zusätzlichen Blatt und die dort definiert, welche Nummer zu welchem Platz gehört. Würde das gehen bzw alles erleichtern?
Gruß marco
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 12:29:58
Marco
Ach so, nein das geht nicht weil die Materialnummern die in einer Zelle stehen zusammen gehören und deren Fehlerhäufigkeit auch. Also so wie es jetzt aufsummiert wird passt es.
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 12:33:49
onur
Was war jetzt genau mit der roten Markierung?
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 12:53:20
Marco
Es sollen ja nur die TOP 3 Fehler angezeigt werden und in meiner Datei wird ein Bereich rot markiert, wenn es mehr als drei Fehlertypen geben sollte, die aber im Dashboard nicht angezeigt werden (siehe Bild). So weiß man ah da gibt es noch mehr die aber nicht so relevant sind, aber man kann ja dann in Data Base nachschauen.
Userbild
Was das beste wäre wenn die Materialnummern und die Stationsnamen in einem extra Blatt wie in meinem unten aufgeführten Beispiel abgelegt sind. Diese könnte man dann jedes mal ändern bzw. mehrere Namen für die gleiche Station oder mehrere Materialnummern für den gleichen Produkttyp vergeben. Wenn das gehen würde wäre es perfekt. Das ist auch mein eigentliches Problem, aber das bekomm ich nicht hin die Zuweisung zu dem ganzen.

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


Vg Marc
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 16:21:27
Marco
Danke Onur, das sieht echt super aus, ist flüssig und übersichtlich, aber kann es sein das der Code seine Grenzen hat? Ich habe in der Datenbank die Liste der Fehler mal bis 10.000 runter kopiert und über 5000 unterschiedliche Fehler erzeugt, dann den Button betätigt, aber dabei hat der Code dann abgebrochen. Ist dein Code auf 100 oder 1000 begrenzt? Nicht das ich jemals 10.000 unterschiedliche Fehler haben werde also das weiß ich nicht, aber ich wollte wissen wie der Code so arbeitet und wo die grenzen sind. Es kann halt sein das ich nach einem jahr vielleicht 50.000 Einträge in der Datenbank haben werde und da habe ich entweder lauter unterschiedliche Fehler also 10.xxx oder sehr viele gleiche Fehler also nur 100 verschiedene aber dafür kommen sie sehr oft vor.
Ich habe vorher noch gefragt ob es möglich wäre die Materialnummern und Stationen in einem anderen Tabellenblatt zu speichern (Beispiel war in der angehängten Datei), damit ich nicht lauter Materialnummern und Stationsnamen in die selbe Zelle schreiben muss sondern, schön übersichtlich untereinander schreibe und somit jeder Zeit neue hinzufügen oder löschen kann?
Danke fei für deine Hilfe Onur
Anzeige
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 17:41:33
onur
Alles eine Sache der Dimensionierung/Einstellung:
For z = 9 To 1000 - Bedeutet, dass bis Zeile 1000 der Fehlermeldungen gesucht wird, kannst du ändern.
Public Fehler(100) As String - Max 100 VERSCHIEDENE Fehlermeldung-Typen
Public Anl(100) As String - Max 100 VERSCHIEDENE Anlagen
Public Mat(100) As Long - Max 100 VERSCHIEDENE Materialien.
Zum Thema "anderes Tabellenblatt" - Könnte man natürlich machen, müsste man nur den ganzen Code wieder ändern.
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 19:10:23
marco
Onur, könntest du mir bitte bitte dabei helfen also bei dem Thema anderes Tabellenblatt, das wäre so viel viel besser.
VG Marc
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 19:13:31
marco
Die Sache mit der Dimensionierung ist mir schon aufgefallen und ich habe es auch geändert und dabei eine Fehlermeldung erhalten "Speicher zu klein" deswegen habe ich dich gefragt ob es dabei eine Grenze gibt bei der Abfrage. Ich habe anstatt 1000 Zeilen mal 100.000 angegeben nur um zusehen was möglich ist.
Grüße Marco
AW: VBA - anstatt suche in Zelle, Array auslesen
25.09.2017 11:52:01
Marco
Hallo Onur, könntest du den Code bezüglich:
"Zum Thema "anderes Tabellenblatt" - Könnte man natürlich machen, müsste man nur den ganzen Code wieder ändern." anpassen ich würde dich dann auch für eine ganz ganz lange Zeit in Ruhe lassen. Also ich schaffe es echt nicht ohne dich. Es wäre halt wirklich sehr nett, wenn du mir diesbezüglich noch einmal helfen könntest.
Viele Grüße Marco
AW: VBA - anstatt suche in Zelle, Array auslesen
25.09.2017 12:20:25
onur
Wahrscheinlich am WE.
Du musst nur mal ein Muster machen, da ich nicht durchblicke, wie genau das hinterher aussehen soll mit der Auswertung.
AW: VBA - anstatt suche in Zelle, Array auslesen
27.09.2017 17:21:58
Marco
Hi Onur,
ich habe dir ein Muster erstellt. Reicht das? Wenn nicht schreib mir bitte dann erstelle ich auf Nacht noch was.
Beispiel Datei:
https://www.herber.de/bbs/user/116590.xlsm
AW: VBA - anstatt suche in Zelle, Array auslesen
27.09.2017 18:03:12
onur
1) Wo ist denn auf dem Dashboard 20001, 20002 usw geblieben?
2) Auf "Materialnummern & Stationen" sind nicht alle Stationen aufgeführt.
AW: VBA - anstatt suche in Zelle, Array auslesen
24.09.2017 16:31:18
Marco
Ich dachte das man die Materialnummern so wie in meinem unten angehängten Beispiel in einem Tabellenblatt schreibt, anstatt in den Zellen im Dashboard und mit dem unten aufgeführten code oder so ähnlich abfragt.
    z = 3
s = 3
Arr_Nummern = Worksheets("Mnummern").Range(Cells(4, 2), Cells(z, s))
For z = 1 To 3
For s = 1 To 3
Debug.Print z, s, Arr_Nummern(z, s)
Next s
Next z

Beispiel-Datei:
https://www.herber.de/bbs/user/116481.xlsm

379 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige