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 11.03.2018, 19:02   #1
spüli
Neuer Benutzer
Neuer Benutzer
Standard Frage - copy/past Problem

Hallo, ich bin neu und habe ein Problem mit VBA Excel 2003:

'Alle Zeilen des Quellblattes "Erfassung", die in der Spalte 'H' den Wert '5' aufweisen, sollen in 'das Blatt "Archiv" verschoben werden und 'anschliessend vom Quellblatt entfernt werden

For n = 3 To lastline 'von erster bis letzte zeile ...
If Worksheets("Erfassung").Range("H" & n).Value = 5 Then 'markierte zeile gefunden
Worksheets("Erfassung").Range("A" & n).EntireRow.Copy 'zeile im erfassungsblatt kopieren
Worksheets("Archiv").Range("A" & (lastlinearchiv)).EntireRow.Select 'zielbereich im archiv vorwählen
Worksheets("Archiv").Range("A" & (lastlinearchiv)).EntireRow.Paste 'kopierten bereich einfügen
lastlinearchiv = lastlinearchiv + 1 'nächste zeile im zielblatt
' Range("A" & n).entirerow.Select 'quellbereich auswählen
' Selection.EntireRow.Delete '.. und entfernen
End If
Next n


Es kommt immer (sinngemäss) die Meldung, dass diese Aktion nicht möglich ist. Was mache ich falsch?
spüli ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.03.2018, 19:31   #2
Crazy Tom
MOF Profi
MOF Profi
Standard

Hallo

lass doch einfach mal das sch... select wech

MfG Tom

__________________

Rückmeldung wäre nett
getestet mit xl2010
Crazy Tom ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 06:16   #3
MisterBurns
MOF Koryphäe
MOF Koryphäe
Standard

Zitat:

Was mache ich falsch?

Womöglich vieles. Am falschesten aber ist eine Aussage wie

Zitat:

Es kommt immer (sinngemäss) die Meldung

Das ist wie wenn du in der Werkstatt sagst, das Auto springt sinngemäß nicht an und du hast ein Foto deines Autos mitgebracht, damit man eine Lösung finden kann.

Also lade bitte eine Beispieldatei hoch, wo der Fehler nachzuverfolgen ist. Und zwar exakt und nicht sinngemäß.

__________________

Schöne Grüße
Berni
MisterBurns ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 06:33   #4
RPP63neu
MOF Meister
MOF Meister
Standard

Moin!
"Falsch" (eher unnötig) ist die Schleife.
Sie verlangsamt das Makro unnötig!
Filtere Spalte H nach 5 und kopiere das Filtrat in die andere Tabelle.
In gefilterten Listen werden nur sichtbare Zellen kopiert!
Wenn es immer noch zu "langsam" ist, also klar über 1/100stel-Sekunde dauert, kannst Du Spalte H vorher sortieren.

Schaue Dir zusätzlich noch die Hilfe zur Range.Copy-Methode an!
Da wird KEINE .Paste-Methode benötigt (die vom Rekorder vorgegaukelt wird):
https://msdn.microsoft.com/de-de/vba...y-method-excel

Gruß Ralf

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 06:36   #5
Werner.M
MOF User
MOF User
Standard

Hallo,

Code:

For n = 3 To lastline 'von erster bis letzte zeile ...
    If Worksheets("Erfassung").Range("H" & n).Value = 5 Then
        Worksheets("Erfassung").Rows(n).Copy Worksheets("Archiv").Rows(lastlinearchiv)
        lastlinearchiv = lastlinearchiv + 1
        Worksheets("Erfassung").Rows(n).Delete
    End If
Next n
Paste alleine ist nicht, wenn dann PasteSpecial. Als Beispiel:
Code:

Worksheets("Archiv").Range("A" & (lastlinearchiv)).EntireRow.PasteSpecial Paste:=xlValues 'nur Werte
Worksheets("Archiv").Range("A" & (lastlinearchiv)).EntireRow.PasteSpecial Paste:=xlAll 'alles
Worksheets("Archiv").Range("A" & (lastlinearchiv)).EntireRow.PasteSpecial Paste:=xlFormats 'nur Formate
Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 06:38   #6
RPP63neu
MOF Meister
MOF Meister
Standard

Wow!
Der auf obiger Hilfeseite vorgestellte Code macht ja genau den gleichen Scheiß!
Von einem Newbie kann man das ja erwarten, aber von Bill Jelen, immerhin MVP und Verfasser von 12 Excel-Büchern, hätte ich einen solchen Stümpercode jetzt nicht erwartet …

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok

Geändert von RPP63neu (12.03.2018 um 08:27 Uhr). Grund: "von" ergänzt
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 06:43   #7
RPP63neu
MOF Meister
MOF Meister
Standard

@Werner:
Gönne Dir mal den Vergleich der Schleifenlösung mit meiner Filterlösung.
Meine dürfte 1000mal schneller ablaufen.
Hab gerade etwas Zeit und werde das gleich mal verifizieren.

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 07:35   #8
RPP63neu
MOF Meister
MOF Meister
Standard

Soderle!
Voraussetzung:
Leere Mappe mit zwei Worksheets.
Folgendes Konstruktionsmakro füllt Tabelle1 mit 10.000 Zeilen, wobei Spalte H zu ca. 10% mit der 5 gefüllt wird.
Das Konstruktionsmakro wird jeweils durch die nachfolgenden Makros aufgerufen.
Sub FillTable()
Application.ScreenUpdating = False
Randomize Timer
With Tabelle1
  .Activate
  .Cells.Clear
  .Range("A1:K1").Formula = "=LEFT(ADDRESS(1,COLUMN(),4),1)"
  .Range("A2:A10000").Formula = "=ROW()"
  .Range("B2:K10000").Formula = "=ADDRESS(ROW(),COLUMN(),4,1)"
  .Range("H2:H10000").Formula = "=TRUNC(RAND()*10,0)+1"
  .UsedRange.Copy: .Cells(1).PasteSpecial xlPasteValues
  .Cells(1).Select
  .Columns.AutoFit
  If Not .AutoFilterMode Then .UsedRange.AutoFilter
End With
Tabelle2.Cells.Clear
With Tabelle2.Range("A1:K1")
  .Formula = "=LEFT(ADDRESS(1,COLUMN(),4),1)"
  .Formula = .Value
End With
Application.CutCopyMode = False
End Sub


1. Zunächst die Schleifen-Variante.
Gnädigerweise habe ich das ScreenUpdating ausgeschaltet.
Laufzeit: ca. 4,5 Sekunden
Sub Schleife()
Dim i#, Start&
Call FillTable
Application.ScreenUpdating = False
Start = Timer
With Tabelle1
  For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
    If .Cells(i, 8) = 5 Then
      .Rows(i).Copy Tabelle2.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End If
  Next
End With
Debug.Print Timer - Start
End Sub


2. Variante mit unsortiertem Autofilter.
Laufzeit: 0,35 Sekunden, also fast 13mal schneller:
Sub Filter_ohne_Sort()
Dim i#, Start&
Call FillTable
Application.ScreenUpdating = False
Start = Timer
With Tabelle1.UsedRange
  .AutoFilter 8, 5
  .Offset(1, 0).Copy Tabelle2.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
  .AutoFilter
End With
Debug.Print Timer - Start
End Sub


3. Varianten mit zweimaligem Sortieren, so dass die Ausgangsposition in Tabelle 1 wiederhergestellt ist.
Laufzeit: 0,01 Sekunden, also 450mal schneller!
Sub Filter_mit_Sort()
Dim i#, Start&
Call FillTable
Application.ScreenUpdating = False
Start = Timer
With Tabelle1.UsedRange
  .Sort .Range("H2")
  .AutoFilter 8, 5
  .Offset(1, 0).Copy Tabelle2.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
  .AutoFilter
  .Sort .Range("A2")
End With
Debug.Print Timer - Start
End Sub


Noch Fragen?

Gruß Ralf

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok

Geändert von RPP63neu (12.03.2018 um 07:38 Uhr).
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 07:43   #9
RPP63neu
MOF Meister
MOF Meister
Standard

Korrektur des letzten Makros:
Ich musste beim Sortieren noch angeben, dass eine Überschrift vorhanden ist:
Sub Filter_mit_Sort()
Dim i#, Start&
Call FillTable
Application.ScreenUpdating = False
Start = Timer
With Tabelle1.UsedRange
  .Sort .Range("H2"), Header:=xlYes
  .AutoFilter 8, 5
  .Offset(1, 0).Copy Tabelle2.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
  .AutoFilter
  .Sort .Range("A2"), Header:=xlYes
End With
Debug.Print Timer - Start
End Sub

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 07:56   #10
EarlFred
MOF Guru
MOF Guru
Standard

@Ralf

Zitat:

Bill Jelen, immerhin MVP und Verfasser von 12 Excel-Büchern

Du untertreibst: da steht sogar was von "über 2 Dutzend Bücher". Aber kein Wunder: Der Spaghetti-Code ist so lang, da füllt man halt schnell viel Papier...
...und wenn ich mir anschaue, was für Bullshitcode seit Jahren bei der Range.Find-Methode in der Hilfe steht...


@TE, @Werner
Code:

For n = 3 To lastline 'von erster bis letzte zeile ...
'...
        Worksheets("Erfassung").Rows(n).Delete
    End If
Next n
Zeilen löschen !!NIEMALS!! in Vorwärtsschleifen.

__________________

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 (12.03.2018 um 08:03 Uhr).
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 09:00   #11
RPP63neu
MOF Meister
MOF Meister
Standard

Zitat: von EarlFred Beitrag anzeigen

...und wenn ich mir anschaue, was für Bullshitcode seit Jahren bei der Range.Find-Methode in der Hilfe steht...

Ich schätze mal, Du meinst dieses begnadete Werk, EarlFred:
Code:

With Worksheets(1).Range("a1:a500") 
    Set c = .Find(2, lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = c.Address 
        Do 
            c.Value = 5 
            Set c = .FindNext(c) 
        Loop While Not c Is Nothing And c.Address <> firstAddress 
    End If 
End With
Ich täte ja so:
Code:

Tabelle1.Range("A:A").Replace(2, 5)
(sieht aber nicht so wichtig aus und berücksichtigt ebenso wie das Beispiel nicht die gespeicherten Parameter der jeweiligen Methode)

Außerdem dürfte es ca. 1000mal schneller laufen …

Gruß Ralf

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok

Geändert von RPP63neu (12.03.2018 um 09:11 Uhr).
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 12:44   #12
EarlFred
MOF Guru
MOF Guru
Standard

Zitat:

Ich schätze mal, Du meinst dieses begnadete Werk

genau jenes!
Lässt man mal die Frage nach geeigneteren Methoden außen vor: Auch als Muster für die Find-Methode enthält der Code gravierende Fehlerquellen. Mich wundert, wie sowas so viele Excel-Generationen überleben kann.

__________________

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 12.03.2018, 14:32   #13
spüli
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard copy & past Probleme

Hallo Werner M.
Danke für die Antwort. Es kamen unerwartet viele, aber Deine war die einfachste, kürzeste und genau das, was ich gesucht hatte. Funktioniert prima und das ganz ohne meterlange Listings.
Ich komme aus dem VB6 Bereich und das Umdenken fällt mir nicht immer leicht.
Gruss, spüli
spüli ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 14:41   #14
EarlFred
MOF Guru
MOF Guru
Standard

@Te
es steht dir frei, wohlmeinende Vorschläge zu ignorieren. Den 2. Hinweis aus Beitrag #10 würde ich mir dennoch anschauen, denn dass Dein / Werners Code immer das tut, was er soll, ist keineswegs garantiert, sondern eher Zufall.

__________________

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 12.03.2018, 17:11   #15
RPP63neu
MOF Meister
MOF Meister
Standard

Zitat: von spüli Beitrag anzeigen

Hallo Werner M.
Danke für die Antwort. Es kamen unerwartet viele, aber Deine war die einfachste, kürzeste und genau das, was ich gesucht hatte. Funktioniert prima und das ganz ohne meterlange Listings.

Nachdem ich meine Brille (neu und verdammt teuer) nochmal per Ultraschall gereinigt habe …
… musste ich feststellen, dass mein Vorschlag im Code #2 (Sub Filter_ohne_Sort()) nicht nur schneller, sondern auch kürzer ist.
Ich weiß beim besten Willen nicht, wo Du da "meterlange Listings" siehst.

Wenn Du aus dem "VB6-Bereich" kommst, solltest gerade Du wissen, dass man geeignete Methoden bevorzugen sollte …

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok
RPP63neu 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 01:03 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.