Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Makro dauert zu lange (>15min).... wie beschleunigen?
zurück: Link von Datei herausbekommen und in Excel schreiben weiter: Wenn- Abfrage mit Commandbutton... Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Feedback Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
Robert S
VBA-Fortgeschritt. dank eingenstudium u Forum


Verfasst am:
17. Sep 2010, 10:23
Rufname: Rob

Makro dauert zu lange (>15min).... wie beschleunigen? - Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Hallo zusammen,

ich habe ein kleine Aufgabe für uns vorbereitet Smile

Ich habe folgendes Makro geschrieben:


Code:
Option Explicit
Public Const ws1 = "MATBELEGE"
Public Const ws2 = "INPUT_FEHLT"
Public Const ws3 = "FEHLTEILE"
Public Const ws4 = "MATBELEGE_UND_FEHLTEILE"
Public Const ws5 = "Parameter"

Public zmat, zwebuchdt, zmatbel, zbestellnum, zbestellpos, zLort, zK, zWeMeng As Integer
Public wbopen, strpfd As String
Public wstoopen, wstoorder As String



Code:
Sub verketten_matbel_fehlt()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim zmatbel_ende As Integer
Dim zzaehler_verketten, zzaheler_fehlteile As Integer
Dim zfehl_Ende As Integer
Dim i, zmat1, zwebuchdt1, zmatbel1, zbestellnum1, zbestellpos1, zLort1, zK1, zWeMeng1 As Integer
Dim zmat2, zwebuchdt2, zmatbel2, zbestellnum2, zbestellpos2, zLort2, zK2, zWeMeng2 As Integer
zmat1 = 0
zwebuchdt1 = 0
zmatbel1 = 0
zbestellnum1 = 0
zbestellpos1 = 0
zLort1 = 0
zK1 = 0
zWeMeng1 = 0
zmat2 = 0
zwebuchdt2 = 0
zmatbel2 = 0
zbestellnum2 = 0
zbestellpos2 = 0
zLort2 = 0
zK2 = 0
zWeMeng2 = 0
zmatbel_ende = ThisWorkbook.Worksheets(ws1).Cells(1, Columns.Count).End(xlToLeft).Column
zfehl_Ende = ThisWorkbook.Worksheets(ws3).Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To zmatbel_ende
   
    Select Case ThisWorkbook.Worksheets(ws1).Cells(1, i).Value
    Case "MatBeleg"
        zmatbel1 = i
    Case "LOrt"
        zLort1 = i
    Case "Bestellung"
        zbestellnum1 = i
    Case "  Pos"
        zbestellpos1 = i
    Case "Material"
        zmat1 = i
    Case "WE-BuDat."
        zwebuchdt1 = i
    Case "K"
        zK1 = i
    Case "      WE-Menge"
        zWeMeng1 = i
    Case Else
    End Select

Next i

For i = 1 To zfehl_Ende
   
    Select Case ThisWorkbook.Worksheets(ws3).Cells(1, i).Value
    Case "Belegnr."
        zmatbel2 = i
    Case "LOrt"
        zLort2 = i
    Case "Bestellung"
        zbestellnum2 = i
    Case "  Pos"
        zbestellpos2 = i
    Case "Material"
        zmat2 = i
    Case "WE-BuDat."
        zwebuchdt2 = i
    Case "K"
        zK2 = i
    Case "      WE-Menge"
        zWeMeng2 = i
    Case Else
    End Select

Next i

'Matbelege in Matbelege_und_Fehlteile kopieren
ThisWorkbook.Worksheets(ws1).Cells.Copy Destination:=ThisWorkbook.Worksheets(ws4).Range("A1")
'Fehlteile kopieren und in entsprechende Zeile einfügen


With ThisWorkbook.Worksheets(ws4)
    For zzaheler_fehlteile = 2 To ThisWorkbook.Worksheets(ws4).Cells(.Rows.Count, 1).End(xlUp).Row
        zzaehler_verketten = 0
        Do
            zzaehler_verketten = zzaehler_verketten + 1
        Loop Until ThisWorkbook.Worksheets(ws3).Cells(zzaehler_verketten, zmatbel2).Value & ThisWorkbook.Worksheets(ws3).Cells(zzaehler_verketten, zbestellpos2).Value = .Cells(zzaheler_fehlteile, zbestellnum1).Value & .Cells(zzaheler_fehlteile, zbestellpos1).Value Or zzaehler_verketten > ThisWorkbook.Worksheets(ws3).Cells(.Rows.Count, 1).End(xlUp).Row
   
    ThisWorkbook.Worksheets(ws3).Range(ThisWorkbook.Worksheets(ws3).Cells(zzaehler_verketten, 1), ThisWorkbook.Worksheets(ws3).Cells(zzaehler_verketten, zfehl_Ende)).Copy Destination:=.Range(.Cells(zzaheler_fehlteile, zmatbel_ende + 1), .Cells(zzaheler_fehlteile, zmatbel_ende + 1 + zfehl_Ende))
   
    Next zzaheler_fehlteile
   
   ThisWorkbook.Worksheets(ws3).Range(ThisWorkbook.Worksheets(ws3).Cells(1, 1), ThisWorkbook.Worksheets(ws3).Cells(1, zfehl_Ende)).Copy Destination:=.Cells(1, zmatbel_ende + 1)

End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Worksheets(ws5).Cells(23, 7).Value = "i.O.!"
End Sub


Dieses Funktioniert einwandfrei, nur dauert der Vorgang mehr als 15 min


Was wird gemacht?
Ich habe 2 Worksheets die ich überprüfe.
Dabei nehme ich im Sheet ws4 nacheinander alle Zeile durch und schaue im Reiter ws3 nach wo dieser Wert vorkommt. Bei übereinstimmung wird die Zeile im Reiter ws3 kopiert und an die entsprechende Zeile im Reiter ws3 angehängt.


Im Reiter ws3 stehen 7500 Einträge und im Reiter ws4 stehen 7800 einträge.


Ich hatte dies sonst immer mit Sverweis hart eingefügt. Da ich aber noch mehr im Makro vorhabe und dies nur ein teilschritt ist, möchte ich komplett auf VBA umsteigen, um die Tätigkeiten leichter übergeben zu können.

wie kann ich das ganze beschleunigen?

Bin für jede HIlfe sehr dankbar!!!


besten Dank schonmal vorab
Gast



Verfasst am:
17. Sep 2010, 10:32
Rufname:


AW: Makro dauert zu lange (>15min).... wie beschleunigen? - AW: Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Anstatt Zelle für Zelle zu durchsuchen, verwende die .Find-Methode!

Jens
EtoPHG
Ich sehe dunkle Zeiten aufziehen...


Verfasst am:
17. Sep 2010, 10:34
Rufname: Hansueli

AW: Makro dauert zu lange (>15min).... wie beschleunigen? - AW: Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Hallo,

1. Formeln sind immer 1000x schneller als VBA Code
2. Niemand wird in der Lage sein, bzw. viel Lust haben, deinen 'komplexen' Code auseinanderzunehmen und damit eine Tabellenstruktur abzuleiten. Eine Beispielmappe wäre hier hilfreicher!
3. Bei grossen Datenbereichen sind ARRAY-Verarbeitungen schneller als DO-LOOPS und FOR-SCHLEIFEN.
4. "...Da ich aber noch mehr im Makro vorhabe " muss zu einem klaren Konzept führen, nach dem dann auch gearbeitet werden kann.

Gruess Hansueli

_________________
Forenhelfer danken für eine Rückmeldung!
Der Kluge lernt, der Dummkopf erteilt gern Belehrungen. - Anton Tschechow
Phelan XLPH
Fortgeschritten


Verfasst am:
17. Sep 2010, 10:41
Rufname: Phelan

AW: Makro dauert zu lange (>15min).... wie beschleunigen? - AW: Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Variante 1) Array+Dictionary
Variante 2) SVerweis-Formel in Hilfsspalte

7500*7800=58.500.000 maliger Zugriff auf eine Zelle
Und die Dauer des Zellzugriff ist um ein vielfaches länger als ein Elementzugriff eines Array

_________________
Was vorstellbar ist, ist auch machbar. - Albert Einstein
Robert S
VBA-Fortgeschritt. dank eingenstudium u Forum


Verfasst am:
17. Sep 2010, 11:09
Rufname: Rob

AW: Makro dauert zu lange (>15min).... wie beschleunigen? - AW: Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Hallo zusammen,

vielen Dank für die schnellen und kostruktiven Antworten!

Stimmt an die .Find methode hatte ich gar nicht gedacht. Das währe eine Idee.

Mit arrays habe ich zwar schon gearbeitet aber kenn mich damit noch nicht sehr damit aus.

Werde mich einarbeiten und bei Bedarf nochmal auf euch zukommen.


Das makro wird nur noch ein wenig ausgeweitet.
es soll anschließend nur noch die Spaltenreihenfolge nach Vorgaben angepasst werden und Formate geändert werden. Aber das bekomme ich hin!

Danke euch!

Grüße
Phelan XLPH
Fortgeschritten


Verfasst am:
17. Sep 2010, 11:35
Rufname: Phelan

AW: Makro dauert zu lange (>15min).... wie beschleunigen? - AW: Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Hallo,

hier mal ein Beispiel mit Array+Dictionary

_________________
Was vorstellbar ist, ist auch machbar. - Albert Einstein



TabellenAbgleichen.xls
 Beschreibung:

Download
 Dateiname:  TabellenAbgleichen.xls
 Dateigröße:  1.16 MB
 Heruntergeladen:  187 mal

Robert S
VBA-Fortgeschritt. dank eingenstudium u Forum


Verfasst am:
17. Sep 2010, 14:49
Rufname: Rob


AW: Makro dauert zu lange (>15min).... wie beschleunigen? - AW: Makro dauert zu lange (>15min).... wie beschleunigen?

Nach oben
       Version: Office 2003

Krass!!!

Dankeschön! Dachte schon, das mit der .Find Methode wie ich es jetzt realisiert habe geht schnell (~30 sec).

Aber das! wohoo o.O Smile

Perfekt das kann cih super verwenden!

Danke dir. So schlicht und sehr verständlich!

Grüße und schönes WE
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Excel Formeln: Priorisierung über Makro aktive Zelle Zeile ausblenden 1 eusebius 1020 08. Nov 2007, 18:20
Melanie Breden Priorisierung über Makro aktive Zelle Zeile ausblenden
Keine neuen Beiträge Excel Formeln: 2 Texte in eine Zelle per Makro ?! 4 ANNAN 683 26. Okt 2007, 23:47
ANNAN 2 Texte in eine Zelle per Makro ?!
Keine neuen Beiträge Excel Formeln: Excel Makro - Summe bilden bei gleichen Suchkriterien 1 Peter1983 3827 26. Sep 2007, 09:39
Alto Excel Makro - Summe bilden bei gleichen Suchkriterien
Keine neuen Beiträge Excel Formeln: automatisches makro 5 sisku46 1297 08. Aug 2007, 09:12
sisku46 automatisches makro
Keine neuen Beiträge Excel Formeln: Brauche Hilfe! Formel und/oder VBA (Makro) 2 ::Patrick:: 2927 01. Aug 2007, 10:42
Gast Brauche Hilfe! Formel und/oder VBA (Makro)
Keine neuen Beiträge Excel Formeln: Sicherheitsabfrage vor Makro schalten 4 Gismo2 1521 10. Jul 2007, 07:49
Gismo2 Sicherheitsabfrage vor Makro schalten
Keine neuen Beiträge Excel Formeln: Namenfeld der Zelle mittels Formel oder Makro benennen 2 cehrat 1181 24. Mai 2007, 16:55
fridgenep Namenfeld der Zelle mittels Formel oder Makro benennen
Keine neuen Beiträge Excel Formeln: Lange Formel mit {} vereinen 2 Maxin 470 05. Mai 2007, 19:16
Maxin Lange Formel mit {} vereinen
Keine neuen Beiträge Excel Formeln: Lese- und Schreibschutz über Makro 0 steven143 876 18. Dez 2006, 13:52
steven143 Lese- und Schreibschutz über Makro
Keine neuen Beiträge Excel Formeln: End up, Markieren, Löschen mit Makro 10 detlef42 1290 21. Nov 2006, 16:13
Detlef 42 End up, Markieren, Löschen mit Makro
Keine neuen Beiträge Excel Formeln: Makro mit Selection.AutoFill 2 GastAlex 3911 18. Okt 2006, 15:25
GastAlex Makro mit Selection.AutoFill
Keine neuen Beiträge Excel Formeln: Extrem lange Wenn / Sverweisformel 5 crusher185 695 28. Sep 2006, 16:15
Gast Extrem lange Wenn / Sverweisformel
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: HTML Editor Forum