Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender Navigationstipps
Inhaltsverzeichnis

Makro statt Sverweis

Makro statt Sverweis
15.05.2015 18:58:28
Michael
Hallo Excelexperten,
ich habe ein Problem und komme nicht so recht weiter.
Ich habe schon vieles probiert aber irgendwie will es nicht funktionieren.
In Tabelle1 möchte ich mir eine Bauteilzuordnung zu jeder Maschine anzeigen lassen, da es extrem viele Daten sind, würde ich dies gern durch ein Makro erledigen lassen. Leider bin ich hier aber noch nicht so richtig fit.
Excel soll die Maschinennummer aus Tabelle1 in Tabelle2 suchen und dann prüfen welche Bauteile zu der Maschinennummer gehören.
Als Ergebnis soll Excel in Tabelle1 für die Bauteile dann eine 1 schreiben.
Ich habe eine Beispieltabelle vorbereitet.
https://www.herber.de/bbs/user/97661.xlsx
Vielleicht könnt Ihr mir weiterhelfen.
Vielen Dank im Voraus
Gruß Michael

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro statt Sverweis
15.05.2015 20:04:09
Daniel
HI
mach eine Auswertung mit Hilfe der Pivottabelle (Einfügen - Tabellen - Pivottable)
ziehe im Assistenten die Maschinennummern nach "Zeilen" und die Kurzbezeichnung und Element nach "Spalten". In den Daten wertest du nach Anzahl für eine beliebige Spalte aus.
in den Pivot-Table-Tools Entwurf kannst du dann die nicht benötigten Teil- und Gesamtergebnisse ausschalten.
Gruß Daniel

AW: Makro statt Sverweis
15.05.2015 20:26:56
Michael
Hallo Daniel,
so arbeite ich im Moment, nur muss ich die 1 überall per Hand setzen.
Da ich auf Grund weiterer Auswertungen und Zuordnungen die Tabelle1 nicht weiter verändern kann.
Ich hatte schon eine Variante mit Wenn und Sverweisformel, nur wird die Mappe dadurch extrem langsam.
Ich möchte mit dem Makro die Tabelle an diesem Punkt automatisieren, da die Liste in Tabelle 2 mindestens 1x am Tag aktualisiert wird. Bei mehr als 400 Maschinen ist das auswerten sehr Zeitintensiv mit der Pivot Lösung.
Gibt es da eine Möglichkeit mit einem Makro?
Vielen Dank
Gruß Michael

Anzeige
AW: Makro statt Sverweis
16.05.2015 00:46:09
Nepumuk
Hallo,
teste mal:
Option Explicit

Public Sub RefreshData()
    
    Dim ialngMachine As Long, ialngValues As Long, lngLastDataRow As Long
    Dim avntValues() As Variant, avntMachine() As Variant, avntOutput() As Variant
    
    With Tabelle2
        avntValues = .Range(.Cells(2, 1), .Cells(.Rows.Count, 3).End(xlUp)).Value2
    End With
    
    With Tabelle1
        
        lngLastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range(.Cells(12, 2), .Cells(lngLastDataRow, 9)).ClearContents
        
        Redim avntOutput(1 To lngLastDataRow, 1 To 8)
        
        avntMachine = .Range(.Cells(12, 1), .Cells(lngLastDataRow, 1)).Value2
        
        For ialngMachine = LBound(avntMachine) To UBound(avntMachine)
            
            For ialngValues = LBound(avntValues) To UBound(avntValues)
                
                If avntValues(ialngValues, 3) = avntMachine(ialngMachine, 1) Then
                    
                    Select Case avntValues(ialngValues, 1)
                        Case "S1"
                            avntOutput(ialngMachine, 1) = 1
                        Case "S2"
                            avntOutput(ialngMachine, 2) = 1
                        Case "B10"
                            avntOutput(ialngMachine, 3) = 1
                        Case "B13"
                            avntOutput(ialngMachine, 4) = 1
                        Case "SCU"
                            avntOutput(ialngMachine, 5) = 1
                        Case "SCZ"
                            avntOutput(ialngMachine, 6) = 1
                        Case "D14"
                            avntOutput(ialngMachine, 7) = 1
                        Case "D15"
                            avntOutput(ialngMachine, 8) = 1
                    End Select
                End If
            Next
        Next
        
        .Range(.Cells(12, 2), .Cells(lngLastDataRow, 9)).Value = avntOutput
        
    End With
End Sub

Gruß
Nepumuk

Anzeige
AW: Makro statt Sverweis
16.05.2015 12:30:50
Michael
Hallo Nepumuk,
vielen Dank für das Makro. In der Beispieltabelle funktioniert es wunderbar.
Jetzt wollte ich es auf die eigentliche Tabelle anwenden, aber da funktioniert es nicht. Ich habe schon die Spalten und Zellen angepasst aber irgendwie will es nicht so wie es soll.
Hier stehen die Maschinennummern in Z27 bis Z500, diese sollen in Tabelle2 H2 bis H100000 gesucht werden.
Wenn er sie dort findet soll er in Tabelle2 von E2 bis E100000 die Bauteile suchen, die in Tabelle1 von AD25 bis CF25 vorgegeben sind. Wenn er diese Übereinstimmungen gefunden hat, soll er in den Spalten AD bis CF jeweils von Zeile 27 bis 500 richtig die 1 zuordnen.
Ich habe zum besseren Verständnis die Beispieltabelle nochmal angepasst.
https://www.herber.de/bbs/user/97667.xlsx
Vielen Dank im Voraus
Gruß Michael

Anzeige
AW: Makro statt Sverweis
16.05.2015 12:54:58
Nepumuk
Hallo,
wieso zeigst du erst ein falsches Muster wenn du kein VBA kannst und daher auch nicht in der Lage bist ein Makro anzupassen?
Option Explicit

Public Sub RefreshData()
    
    Dim ialngMachine As Long, ialngValues As Long, lngLastDataRow As Long
    Dim avntValues() As Variant, avntMachine() As Variant, avntOutput() As Variant
    
    With Tabelle2
        avntValues = .Range(.Cells(2, 5), .Cells(.Rows.Count, 8).End(xlUp)).Value2
    End With
    
    With Tabelle1
        
        lngLastDataRow = .Cells(.Rows.Count, 26).End(xlUp).Row
        
        .Range(.Cells(27, 30), .Cells(lngLastDataRow, 84)).ClearContents
        
        Redim avntOutput(1 To lngLastDataRow - 26, 1 To 55)
        
        avntMachine = .Range(.Cells(27, 26), .Cells(lngLastDataRow, 26)).Value2
        
        For ialngMachine = LBound(avntMachine) To UBound(avntMachine)
            
            For ialngValues = LBound(avntValues) To UBound(avntValues)
                
                If avntValues(ialngValues, 4) = avntMachine(ialngMachine, 1) Then
                    
                    Select Case avntValues(ialngValues, 1)
                        Case "S1"
                            avntOutput(ialngMachine, 1) = 1
                        Case "S2"
                            avntOutput(ialngMachine, 2) = 1
                        Case "B10"
                            avntOutput(ialngMachine, 3) = 1
                        Case "B13"
                            avntOutput(ialngMachine, 4) = 1
                        Case "SCU"
                            avntOutput(ialngMachine, 5) = 1
                        Case "SCZ"
                            avntOutput(ialngMachine, 6) = 1
                        Case "D14"
                            avntOutput(ialngMachine, 7) = 1
                        Case "D15"
                            avntOutput(ialngMachine, 8) = 1
                    End Select
                End If
            Next
        Next
        
        .Range(.Cells(27, 30), .Cells(lngLastDataRow, 84)).Value = avntOutput
        
    End With
End Sub

Gruß
Nepumuk

Anzeige
AW: Makro statt Sverweis
16.05.2015 14:36:00
Michael
Hallo Nepumuk,
super, vielen Dank für deine Hilfe. Jetzt funktioniert es.
Ich habe auch den Fehler gefunden, den ich gemacht habe.
Ich bin statt 84 Spalten nur von den Spalten ausgegangen, in denen die Daten stehen oder rein sollen.
Trotzdem nochmal vielen, vielen Dank
Gruß Michael

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige