Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Range innerhalb For each RG Schleife neu zuweisen

Betrifft: Range innerhalb For each RG Schleife neu zuweisen von: Roland
Geschrieben am: 23.06.2015 07:50:04

Hallo, ich habe eine Frage und hoffe mir kann jemand hierbei helfen.

ich arbeite eine Tabelle per

"For each RG in Range"

ab und bearbeite jede Zelle.
Zu beginn einer jeden Zeile frage ich jedoch eine bestimmte Zelle auf bestimmte Inhalte ab und wenn dies zutrifft dann loesche ich die komplette Zeile und gehe zu

"next RG"

Nun muss ich jedoch meinen RG auf einen anderen Bereich zuruecksetzen damit ich keine Zeile und vor Allem auch keine Zelle verpasse zu bearbeiten. Bei einer

"For i = x to y"- schleife kann ich den Wert der in i steht einfach auf einen anderen wert zuruecksetzen (z.B. von 100 auf 99) damit die Schleife, wenn ich die Zeile 100 geloescht habe i auf 99 setze und dann bei
"next i"
der wert durch die Schleife automatisch wieder auf 100 gesetzt wird. Somit vermeide ich dass ich beider Abarbeitung nach dem loeschen einer Zeile eine Zeile verpasse.
Ich habe aber keine Ahnung wie ich das mit einer
"For each RG in Range" - Schleife machen kann. Habe schon alles moegliche probiert aber ohne erfolg.
Wie kann ich RG auf einen anderen Wert setzen ?

Kann mir hier jemand helfen ?

Vielen Dank im Voraus.

Roland

  

Betrifft: Schleife rückwärts ... von: Matthias L
Geschrieben am: 23.06.2015 08:16:06

Hallo

Vorschlag:

Benutze eine Schleife die rückwärts läuft

z.B.

for i = 100 to 1 step -1
 'hier Deine Bedingung
next
Dann brauchst Du i auch nicht immer neu anpassen.

Gruß Matthias


  

Betrifft: AW: Range innerhalb For each RG Schleife neu zuweisen von: Werner
Geschrieben am: 23.06.2015 08:17:35

Hallo Roland,

genau aus diesem Grund lässt man bei so etwas die Schleife von der letzten Zeile zur ersten Zeile, also rückwärts laufen. Angenommen du willst von der letzten Zeile bis zur Zeile 2 prüfen und löschen. Die ermittelte letzte Zeile hast du in einer Variablen loLetzte stehen. Dann so:

For i = loLetzte to 2 step -1
Gruß Werner


  

Betrifft: AW: Range innerhalb For each RG Schleife neu zuweisen von: Roland
Geschrieben am: 23.06.2015 09:52:00

Danke fuer Eure bisherigen Antworten aber diese sind nicht die gewuenschte Loesung.
Ich arbeite mit Range und nicht mit Integer. Folgendes Problem:

For Each RG In ActiveSheet.Range("A2:F" & ActiveSheet.UsedRange.Rows.Count)

If Right(Range("G" & RG.Row), 1) = "A" Or Right(Range("H" & RG.Row), 1) = "A" Then
Rows(RG.Row).Delete shift:=xlUp
Else
anderer Code
End If

Next RG

Wenn ich jetzt z.B. RG.Address $F$10 habe und die If-Bedingung zutrifft wird die Zeile 10 geloescht. Die Schleife springt zu Next RG und die RG.Address waere dann $A$11. Da aber die Zeile 10 gel;oescht wurde muss ich wieder bei Adresse $A$10 beginnen und nicht bei $A$11 da ich sonst eine Zeile ausgelassen habe. Ich muss demnach der RG-Variablen sagen dass sie bei einem anderen RG.Address weitermachen soll, also bei $A$10 und nicht bei $A$11. Wie weisse ich der RG-Variablen anstelle der Adresse die ihr durch die Schleife zugewiesen wurde die Adresse zu bei der sie weitermachen soll ????

Gruss
Roland


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Daniel
Geschrieben am: 23.06.2015 10:34:49

Hi

kleiner Tip:
verwende zum Löschen von Zeilen mit Bediungung das Duplikate-Entfernen aus den Daten-Datentools.
das ist deutlich schneller, als jede Zeile einzeln zu prüfen und zu löschen.

das geht im Prinzip so:
1. Schreibe in eine Hilfsspalte am Tabellenende eine Formel, welche alle Zeilen, die gelöscht werden sollen, mit 0 markiert und alle die stehenen bleiben müssen mit der Zeilennummer.
das wäre in deinem Fall: =Wenn(Oder(Links(G1;1)="A";Links(H1;1)="A");0;Zeile())

2. Schreibe in die Überschriftenzeile ebenfalls die 0

3. wende auf die Tabelle die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an, mit der Hilfsspalte als Kriterium und der Option "keine überschrift"

4. Lösche die Hilfsspalte.

die funkition löscht dann alle Zeilen mit 0, ausser der der ersten und das ist die Überschrift, die sowieso stehen bleiben soll

sieht als Makro so aus:



With ActiveSheet.usedRange
    With .Columns(.columns.count + 1)
        .FormulaR1C1 = "=IF(OR(Left(RC7,1)=""A"",Left(RC8,1)=""A""),0,Row())"
        .Cells(1, 1).Value = 0
        .EntireRow.RemoveDuplicates .Column, xlno
        .clearcontents
    end With
End With
das ist nicht nur schneller als eine Schleife, es ist auch in der Testphase des Programms wesentlich angenehmer, denn wenn du im Einzelstep testest, musst du dich nicht durch die Schleife durchklicken oder mit Haltepunkten rumhantieren, sondern du klickst dich einfach durch diese 4 Schritte durch.
ausserdem werden alle Zeilen vor dem Löschen markiert, dh du kannst, bevor du endgültig löschst, einfach prüfen, ob auch die richtigen markiert wurden.

Gruß Daniel


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Roland
Geschrieben am: 23.06.2015 11:14:39

Hallo und vielen Dank fuer Eure Hilfe,

habe verstanden, dass ich der RG-Variabelen keinen anderen Range-Wert zuweisen kann. Trotzdem vielen Dank fuer Eure Anregungen.
Habe in der Zwischenzeit bereits zwei Schleifen geschrieben. Eine fuer die Zeilen zu loeschen und eine fuer alle zellen zu bearbeiten. Ich muss leider alle zellen durchlaufen weil ich alle Zellen noch bearbeiten muss (ausser die letzten beiden Spalten und die letzten beiden Spalten pruefe ich auf Werte um die Zeilen zu loeschen).

Vielleicht werde ich die Idee von Daniel noch aufgreifen.

Also nochmals vielen Dank fuer Eure Hilfe

Roland


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Roland
Geschrieben am: 23.06.2015 14:51:25

Noch eine Frage an Daniel,

kannst Du mir die eine Zeile

.EntireRow.RemoveDuplicates .Column, xlno

naeher erklaeren bitte.
Der erste Teil ".EntireRow.RemoveDuplicates" ist mir klar.
Aber dann kommt mit etwas Abstand ".column, xlno" und das verstehe ich nicht

Danke Roland


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Daniel
Geschrieben am: 23.06.2015 16:05:56

Hi
wenn du das Duplikate-Entfernen ausführst (dh von Hand, nicht mit Excel) musst du zusätzlich zum Zellbereich zwei weitere Daten angeben:
1. die Spalte(n), welche für die Duplikatsprüfung verwendet werden
2. ob in der angegebenen Tabelle eine Überschriftenzeile vorhanden ist oder nicht.
diese beiden Informationen müssen natürlich auch in einen Makro gegeben werden und das passiert hier.

vollständigkeitshalber kann man die Parameter noch benennen:

.RemoveDuplicates Columns:=.Column, Header:=xlNo

aber dieses Benennung der Parameter kann man weglassen, wenn man die Parameter in der richtigen Reihenfolge hinschreibt.

Gruß Daniel


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Roland
Geschrieben am: 23.06.2015 16:21:07

Danke Daniel,

hast Du vielleicht so eine clevere loesung fuer das entfernen von Leerzeichen vor und nach einem Wert ohne verwenden einer Schleife?

Also z.B. Spalte A bis H alle Zellen bearbeiten mit

Zelle = Trim(Zelle)

aber die nachfolgenden Spalten nicht.

Ware super.

Danke

Roland


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Daniel
Geschrieben am: 23.06.2015 16:38:44

Hi
so vielleicht:

with activesheet.Usedrange
    With .columns(.columns.Count + 1).Resize(, 8)
        .formular1c1 = "=Trim(RC[-" & .column - 1 & "])
        .copy
        .cells(1, 1).pastespecial xlpastevalues
        .clearcontents
    end with 
end with

das fügt dann am Tabellenende die Formel ein =Glätten(A1), halt 8 Spalten breit und soviel Zeilen wie vorhanden.
Dann werden die Formelergebnisse als Wert nach A1 zurückkopiert und die Formel wird wieder gelöscht.

alternativ auch per Schleife, das ist ähnlich schnell, wenn man die Werte in ein Array kopiert und dort bearbeitet:
dim arr
dim z as long, s as long
with ActiveSheet.Usedrange.columns(1).Resize(, 8)
    arr = .value
    for z = 1 to ubound(arr, 1)
        for s = 1 to ubound(arr, 2)
            arr(z, s) = Trim(arr(z, s))
        Next s
    next z
    .value = arr
end with

beachte, dass das VBA-Trim etwas anders funktioniert als die Glätten-Funktion in Excel.
die Glätten-funktion löscht nicht nur die Leerzeichen am Anfang und am Ende, die reduziert auch alle mehrfach-Leerzeichen innerhalb des Textes auf ein Leerzeichen.

Gruß Daniel


  

Betrifft: AW: Zeilen löschen: Duplikate entfernen von: Roland
Geschrieben am: 24.06.2015 06:37:35

Danke Daniel fuer Deine Hilfe.
Ich habe gestern Abend eine andere Loesung gefunden fuer Trim().

dim RG as Range
Set RG = ActiveSheet.Range("A1:F" & ActiveSheet.UsedRange.Rows.Count)
RG.Value = Application.TRIM(RG.Value)

klappt super und ist sehr schnell.

Schoenen tag noch

Roland


  

Betrifft: die andere Loesung gefunden fuer Trim ist falsch! von: Matthias L
Geschrieben am: 24.06.2015 07:55:11

Hallo

klappt super und ist sehr schnell
Ist aber falsch.

Nimm mal eine neue Mappe

Schreibe nun nur in C4 und D5 irgend einen Text mit ein paar Leerzeichen.
Lass nun mal den Code laufen:

Dim RG As Range
MsgBox "UsedRange.Address: " & ActiveSheet.UsedRange.Address & vbLf & "letzte Zeile im UsedRange: " & ActiveSheet.UsedRange.Rows.Count, vbInformation
Set RG = ActiveSheet.Range("A1:F" & ActiveSheet.UsedRange.Rows.Count)
RG.Value = Application.Trim(RG.Value)

Den blauen Text bitte in eine Zeile im Code schreiben.
Vor und nach dem &-Zeichen bitte immer ein Leerzeichen im Code benutzen.


Die MsgBox soll nur zum Verstehen dienen.
Du würdest also hier nur in A1:F2 die Leerzeichen entfernen!
Da steht aber nichts!

Sollte das bei Dir nicht klappen hier die Bsp-Datei
https://www.herber.de/bbs/user/98419.xlsm

Gruß Matthias


  

Betrifft: AW: die andere Loesung gefunden fuer Trim ist falsch! von: Daniel
Geschrieben am: 24.06.2015 09:39:13

Hi Matthias

hier gings ja zunächst mal um die Trim-Funktion, und die klappt super.
das kannte ich auch noch nicht, zumal das Trim nach Application gar nicht in der Intellisense angezeigt wird.

das die Zuweisung des Zellbereichs nur für den Fall passt, dass die Usedrange in A1 beginnt, ist in diesem Thread doch eher nensächlich.
das kann man dann auch einfacher gestalten:

set RG = ActiveSheet.Usedragen.Columns(1).Resize(, 6)

oder

set RG = Intersect(Activesheet.UsedRange, ActiveSheet.Range("A:F"))

Gruß Daniel


  

Betrifft: Application.Trim ist Worksheet.Function.Trim ... von: Luc:-?
Geschrieben am: 24.06.2015 14:57:22

…also die XlStandardFkt GLÄTTEN, die sich deutlich anders verhält als die vbFkt Trim, weshalb sie wohl auch im WorksheetFunction-Container-Objekt enthalten ist, Daniel;
auf diese Fktt reagiert der VBE-Intellisense nicht und in der Auswahl­Anzeige gehen sie in der Fülle der Angebote für Application.… unter.
Gruß, Luc :-?


  

Betrifft: AW: Application.Trim ist Worksheet.Function.Trim ... von: Roland
Geschrieben am: 24.06.2015 20:01:23

Also vielen Dank Euch allen fuer die vielen Info's
Alles habe ich noch nicht verstanden (liegt unter Anderem auch an den vielen Abkuerzungen die Ihr benutzt). Muss ich mir bei Gelegenheit nochmals naeher anschauen.
Meine Loesung klappt jedenfalls super fuer das was ich in diesem Fall benoetige.

Also nochmals vielen Dank.

Roland


  

Betrifft: AW: Application.Trim ist Worksheet.Function.Trim ... von: Daniel
Geschrieben am: 25.06.2015 10:36:19

... Sicher Luc?

ich dachte immer, dass die Funktionen aus Application andere sind als die aus Worksheetfunction.

beispielsweise erzeugt

erg = WorksheetFunction.Match("xxx", Range("a:a"), 0)

einen Fehlerabbruch, wenn "xxx" nicht im Suchbereich vorhanden ist,

während
erg = Application.Match("xxx", Range("a:a"), 0)

fehlerfrei durchläuft und der Variable erg dann eben den Fehlerwert zuweist.
(erg ist in beiden Fällen als Variant deklariert)

in sofern hatte ich eigentlich gedacht, dass Application und Worksheetfunction unterschiedliche "Container" sein müssen.

Gruß Daniel


  

Betrifft: Das ist der einzige Unterschied, ... von: Luc:-?
Geschrieben am: 25.06.2015 13:27:11

…Daniel,
weil Application der „große Container“ für alles ist und die Fkt darin keiner „Nachbehandlung“ unterzogen wird, was aber bei WorksheetFunction-Verwendung passiert, wodurch kein Fehlerwert durchgelassen, sondern die Operation einfach abgebrochen wird. Das kannst du ja mal mit einer eigenen UDF, die du in ein Klassenmodul steckst, testen.
Ich gehe also davon aus, dass es sich nur um 2 verschiedene VBA-Zugriffsmethoden auf die gleiche Fkt handelt, wobei der „Container im Container“ quasi ergebnis-normierend wirkt (dafür findet man dort per VBE-Intellisense die im direkten VBA-Zugriff stehenden Xl-Fktt leichter). Es handelt sich also wohl kaum um eine 2-Fach-Pgmierung für VBA, wie verschiedentlich vermutet wird (warum sollte MS das auch tun?!).
Luc :-?


  

Betrifft: AW: Das ist der einzige Unterschied, ... von: Daniel
Geschrieben am: 25.06.2015 14:01:02

Hi
vermutest du jetzt auch nur oder weisst du was gesichert?
was ist der unterschied zwischen einer UDF in einem Klassenmodul und einem Allgemeinen Modul?
warum muss es eine UDF in einem Klassenmodul sein und nicht in einem allgemeinen Modul wie sonst üblich für eine UDF?
Gruß Daniel


  

Betrifft: Weil das bei WorksheetFunction mit Sicherheit ... von: Luc:-?
Geschrieben am: 25.06.2015 14:10:43

…so ist, Daniel;
warum wohl kann man sowohl Application als auch WorksheetFunction den FktsNamen nach Pkt nachstellen…? ;->
Mit einem (bereits vorhandenen) Klassenmodul lässt sich das leichter und schneller testen, als wenn du erst ein AddIn anlegen wolltest, dessen VBAProjekt du dann auch einen eigenen, speziellen CodeNamen geben müsstest…
Luc :-?


  

Betrifft: Das geht so nicht, ... von: Luc:-?
Geschrieben am: 23.06.2015 10:43:06

…Roland,
das haben Matti & Werner versucht, dir zu erklären! Anderenfalls, also mit deinem Schleifentyp, müsstest du das Pgm mehrfach laufen lassen.
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Erst alle sammeln, dann gemeinsam löschen von: Daniel
Geschrieben am: 23.06.2015 10:51:17

Hi
wenn du unbedingt mit der For-Each-Schleife arbeiten willst, könntest du auch zuerst die zu löschenden Zeilen in einer weitern Range-Variable sammeln und dann am Schluss löschen.
Dann hast du das Problem mit dem Überspringen nicht.

ausserdem ist deine Schleife überflüssig lang.
es reicht, wenn die Schleife pro Zeile eine Zelle durchläuft und nicht 6 (A:F):

dim rngLösch as Range
dim RG as Range
For Each RG In ActiveSheet.Range("A2:A" & ActiveSheet.UsedRange.Rows.Count)
    If Right(Range("G" & RG.Row), 1) = "A" Or Right(Range("H" & RG.Row), 1) = "A" Then
       if rngLösch is nothing then 
          set rngLösch = RG
       Else
          set rngLösch = Union(rngLösch, RG)
       end If
    Else
        anderer Code
    End If
Next
rngLösch.entireRow.Delete
Gruß Daniel


 

Beiträge aus den Excel-Beispielen zum Thema "Range innerhalb For each RG Schleife neu zuweisen"