Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema RefEdit
Screenshot zu RefEdit	RefEdit-Seite mit Beispielarbeitsmappe aufrufen

Bereich nur markieren wenn bestimmter Wert | Herbers Excel-Forum

• Bereich nur markieren wenn bestimmter Wert von jens vom 19.01.2010 12:34:07
• AW: Bereich nur markieren wenn bestimmter Wert - von Thomas Hohenester am 19.01.2010 12:45:02
  • AW: Bereich nur markieren wenn bestimmter Wert - von Lutz am 19.01.2010 12:58:16
• Application.Match - von Matthias5 am 19.01.2010 12:46:37
  • AW: Application.Match - von jens am 19.01.2010 13:01:54
    • AW: Application.Match - von Matthias5 am 19.01.2010 13:11:25
    • AW: Application.Match - von Gerd L am 19.01.2010 13:31:22
      • AW: Application.Match - von jens am 19.01.2010 14:14:22
• AW: Bereich nur markieren wenn bestimmter Wert - von Helge Kuehn am 19.01.2010 12:53:44
• AW: Ende nach Formellogik - von Gerd L am 19.01.2010 14:26:16
  • AW: Ende nach Formellogik - von Lutz am 19.01.2010 16:52:32
    • AW: Ende nach Formellogik - von Gerd L am 19.01.2010 17:27:11
      • AW: Ende nach Formellogik - von Lutz am 19.01.2010 18:15:52
        • AW: Ende nach Formellogik - von Gerd L am 19.01.2010 19:36:19
          • AW: Ende nach Formellogik - von Lutz am 20.01.2010 13:26:35
            • AW: Ende nach Formellogik - von Gerd L am 20.01.2010 22:00:31
              • AW: Ende nach Formellogik - von Lutz am 20.01.2010 23:39:23
                • AW: Ende nach Formellogik - von Hajo_Zi am 23.01.2010 08:54:10
                  • AW: Ende nach Formellogik - von Lutz am 23.01.2010 12:45:16
• warum zur Hölle .... - von Rudi Maintaire am 19.01.2010 14:29:51
  • AW: warum zur Hölle .... - von Lutz am 19.01.2010 15:12:01
    • AW: warum zur Hölle .... - von Gerd L am 19.01.2010 15:25:46
      • AW: warum zur Hölle .... - von Lutz am 19.01.2010 17:12:49
        • AW: warum zur Hölle .... - von Uduuh am 19.01.2010 20:51:37
          • AW: warum zur Hölle .... - von Lutz am 20.01.2010 12:42:07

Betrifft: Bereich nur markieren wenn bestimmter Wert von: jens
Geschrieben am: 19.01.2010 12:34:07

Hallo Forumsteilnehmer,

ich möchte einen Bereich markieren - aber nur solange bis ein bestimmter Wert "Ende" auftaucht.

Ich habe im Bereich C1:F25 Formeln.

Bis zu einer bestimmten Zeile stehen in Spalte C irgendwelche Werte und ab dann nur noch "Ende".

Ich möchte jetzt mit einem Makro nur den Bereich markieren - und dann kopieren in dem noch nicht "Ende" steht.

Geht das?

Vielen Dank für Eure Hilfe

Gruß Jens

Betrifft: AW: Bereich nur markieren wenn bestimmter Wert von: Thomas Hohenester
Geschrieben am: 19.01.2010 12:45:02

Hallo Jens,
gehen tut das. Aber warum machst du das nicht mit einer sog. "Bedingten Formel"? Dann mußt du das nicht per Makro machen, sondern Excel macht das automatisch.
Kann höchstens sein, dass es dann die Datei ein wenig aufbläht und es wenig langsamer geht...
Liebe Grüße
Thomas

Betrifft: AW: Bereich nur markieren wenn bestimmter Wert von: Lutz
Geschrieben am: 19.01.2010 12:58:16

Hallo Thomas,

vielen Dank, ich brauche es in einem Makro weil danach noch mehr kommt.

Gruß Lutz

Betrifft: Application.Match von: Matthias5
Geschrieben am: 19.01.2010 12:46:37

Hallo Jens,

zeile = Application.Match("Ende", Range("C:C"), 0) - 1
Range("C1:F" & zeile).Copy

Ggf. musst du noch den Fall abfangen, dass "Ende" gar nicht vorkommt, falls das möglcih ist.

Gruß,
Matthias

Betrifft: AW: Application.Match von: jens
Geschrieben am: 19.01.2010 13:01:54

Hallo Matthias,

das scheint zu gehen - ich hatte das etwas vereinfacht.

Bei mir steht das im Bereich W4:BM23 und wenn ich versuche das anzupassen dann geht es nicht mehr.

Wie müßte das für den Bereich aussehen?

Und: wie kann ich denn abfangen, dass er etwas falsch macht, wenn bis W23 immer etwas anderes als Leerdatensatz steht?

Vielen Dank Gruß Jens

Betrifft: AW: Application.Match von: Matthias5
Geschrieben am: 19.01.2010 13:11:25

Hallo Jens,

steht das Signalwort ("Ende") denn immer in Spalte W? Dann einfach so:

zeile = Application.Match("Ende", Range("W4:W23"), 0) + 2
Range("W4:BM23" & zeile).Copy

wie kann ich denn abfangen, dass er etwas falsch macht, wenn bis W23 immer etwas anderes als Leerdatensatz steht?

Das verstehe ich nicht ganz, was meinst du damit?

Gruß,
Matthias

Betrifft: AW: Application.Match von: Gerd L
Geschrieben am: 19.01.2010 13:31:22

Hallo Jens!

Sub egal()
Dim R As Range

If Range("W4").Text <> "Ende" Then
If Application.CountIf(Range("W4:W23"), "Ende") > 0 Then
    Set R = Range("W4:BM" & Application.Match("Ende", Range("W4:W23"), 0) + 2)
End If
End If

If Not R Is Nothing Then
    R.Select
End If

End Sub

Gruß Gerd

Betrifft: AW: Application.Match von: jens
Geschrieben am: 19.01.2010 14:14:22

Hallo Gerd,

das sieht eigentlich sehr gut aus, selektiert/kopiert aber nichts.

Warum weiß ich auch nicht.

Vielleicht muß man noch sagen: wenn W23 <> Ende dann W1:BM23 selektieren?

Das Problem ist immer, wenn W23 noch einen anderen Wert als Ende hat (d.h. alle 20 Datensätze sind gefüllt)


Gruß Jens

Betrifft: AW: Bereich nur markieren wenn bestimmter Wert von: Helge Kuehn
Geschrieben am: 19.01.2010 12:53:44

Bidde:

Nur die richtigen spalten einfuegen und mit alt+F8 laufen lassen

Sub Worksheet_Calculate()
     Dim zelle As Long
    Dim i As Long
    Range("A:A").Interior.ColorIndex = xlNone
    zelle = Cells(Rows.Count, 1).End(xlUp).Row
    For i = zelle To 1 Step -1
    If Range("A" & i) = "Ende" Then
    Range("A" & i).Interior.ColorIndex = 3
    End If
    Next
End Sub

Gruss

Betrifft: AW: Ende nach Formellogik von: Gerd L
Geschrieben am: 19.01.2010 14:26:16


Hallo Jens,

es ist nicht so ganz einfach, der unbekannten Formel-Syntax Rechnung zu tragen.
Nächster Versuch:

Sub egal2()
Dim R As Range

Set R = Range("W4:BM23")

If Range("W4").Text <> "Ende" Then
If Application.CountIf(Range("W4:W23"), "Ende") > 0 Then
    Set R = Range("W4:BM" & Application.Match("Ende", Range("W4:W23"), 0) + 2)
End If
End If

R.Select

End Sub

Gruß
Gerd

Betrifft: AW: Ende nach Formellogik von: Lutz
Geschrieben am: 19.01.2010 16:52:32

Hallo Gerd,

vielen Dank.

Das geht wie verrückt. Mit den beiden Verbesserungen kommen wir von ca. 10 sekunden (gefühlt eine Minute) auf nicht mal eine sekunde.

Eine Frage vielleicht noch:

Wir haben in der Firma Excel 2003 und 2007 im Einsatz.

Mit Sheets("Liste").Range("A65536").End(xlUp) verschenkt man bei Excel 2007 ja viele Zeilen. Kann man eigentlich statt End(xlup) das ganze auch mit End(xldown) machen? Gibt es das überhaupt?

Auf jeden Fall schon mal tausend Dank für Deine Hilfe

Jens und Lutz

Betrifft: AW: Ende nach Formellogik von: Gerd L
Geschrieben am: 19.01.2010 17:27:11

Hallo die Herren,

ja das gibt es. Schaut aber zuerst zu der End-Funktion in der VBA-Hilfe nach.
Die Funktionsweisen sind dort gut beschrieben.
Testet manuell mit den Tasten, ob die Daten dazu passen. Das ist wichtiger.

Wenn es einen Laufzeit-Unterschied gibt, dürfte dieser nur marginal sein.

Gruß Gerd

Betrifft: AW: Ende nach Formellogik von: Lutz
Geschrieben am: 19.01.2010 18:15:52

Hallo Gerd,

vielen Dank - wir hatten da mal reingesehen aber da steht nur:

Ausdruck Erforderlich. Ein Ausdruck, der eines der Objekte der Liste Betrifft zurückgibt.

Direction Erforderlicher XlDirection-Wert. Die Richtung, in die verschoben wird.

XlDirection kann eine der folgenden XlDirection-Konstanten sein.
xlDown
xlToRight
xlToLeft
xlUp

Beispiel
In diesem Beispiel wird die oberste Zelle in Spalte B in dem Bereich markiert, der die Zelle B4 enthält.

Range("B4").End(xlUp).Select

In diesem Beispiel wird die Zelle am Ende der vierten Zeile in dem Bereich markiert, der die Zelle B4 enthält.

Range("B4").End(xlToRight).Select

In diesem Beispiel wird die Markierung von Zelle B4 bis zur letzten Zelle in der vierten Zeile erweitert, die Daten enthält.

Worksheets("Sheet1").Activate
Range("B4", Range("B4").End(xlToRight)).Select

Leider kein End(xldown) - Beispiel und wir haben das nicht hinbekommen. Auch im Herber-Archiv war nichts brauchbares.

Die Laufzeitverbesserung kam durch Deinen Code in dem wir nicht mehr lange sortieren mußten...- vielen Dank.

Weißt Du wie das mit End(xldown) geht? Wäre sehr nett und es würde dann auf allen unseren Rechnern gehen (auch wenn allle auf 2007 umgestellt sind).

Vielen Dank Jens und Lutz

Betrifft: AW: Ende nach Formellogik von: Gerd L
Geschrieben am: 19.01.2010 19:36:19

Hallo Jens u. Lutz,

der Satz aus der Hilfe für 's manuelle Ausprobieren.
Diese Methode entspricht dem Drücken von ENDE + NACH-OBEN, ENDE + NACH-UNTEN, ENDE + NACH-LINKS oder ENDE + NACH-RECHTS.

Beispiel für A2 "abwärts"
With Worksheets(1)
.Cells(IIf(Len(.Cells(2, 1)) = 0, 2, IIf(Len(.Cells(3, 1)) = 0, 3, .Cells(2, 1).End(xlDown).Row + 1)), 1).Select
End With

Aber macht euch keine Sorgen. End(xlUp) macht das auch in xl2007 noch sehr gut.

Gruß Gerd

Betrifft: AW: Ende nach Formellogik von: Lutz
Geschrieben am: 20.01.2010 13:26:35

Hallo Gerd,


irgendwie hat das Forum etwas Kummer - stellt die Antwort nicht richtig ein.

Ich habe es mal mit der Syntax:

Sheets("Liste").Cells(IIf(Len(.Cells(2, 1)) = 0, 2, IIf(Len(.Cells(3, 1)) = 0, 3, .Cells(2, 1).End(xlDown).Row + 1)), 1).PasteSpecial Paste:=xlPasteValues

versucht aber das geht nicht. Beim Fett markierten steigt er aus.

Habe ich wahrscheinlich was falsch gemacht?!

Gruß Lutz

Betrifft: AW: Ende nach Formellogik von: Gerd L
Geschrieben am: 20.01.2010 22:00:31

Hallo Lutz,

bei der Cells-Schreibweise muss die Blattangabe jeweils (wiederholt) vor Cells
oder der Code in einen With-EndWith - Block geschrieben werden, dann reicht als Verweis der Punkt

Sheets("Liste").Cells(IIf(Len(Sheets("Liste").Cells(2, 1)) = 0, 2, IIf(Len(Sheets("Liste").Cells(3, 1)) = 0, 3, Sheets("Liste").Cells(2, 1).End(xlDown).Row + 1)), 1).PasteSpecial Paste:=xlPasteValues

Gruß Gerd

Betrifft: AW: Ende nach Formellogik von: Lutz
Geschrieben am: 20.01.2010 23:39:23

Hallo Gerd,

touche - das geht wie verrückt.

Ich bin schwer beeindruckt.

Vielen, vielen Dank, Du hast uns wirklich sehr geholfen.

Ich wünsche Dir noch einen schönen Abend/gute Nacht.

Nochmal vielen Dank

Gruß Lutz

Betrifft: AW: Ende nach Formellogik von: Hajo_Zi
Geschrieben am: 23.01.2010 08:54:10

Hallo Lutz,

warum ist der Beitrag als offen gekennzeichnet. Ich kann keinen Grund in Deinem Beitrag sehen.

Betrifft: AW: Ende nach Formellogik von: Lutz
Geschrieben am: 23.01.2010 12:45:16

Hallo Hajo,

der Thread ist natürlich erledigt - sorry.

Gruß Lutz

Betrifft: warum zur Hölle .... von: Rudi Maintaire
Geschrieben am: 19.01.2010 14:29:51

machst du mehrere Threads unter verschiedenen Namen auf, Jens-Lutz-Lars?

Gruß
Rudi

Betrifft: AW: warum zur Hölle .... von: Lutz
Geschrieben am: 19.01.2010 15:12:01

Hallo Rudi,

sorry mein Kollege und ich haben beide einen Account und wir haben ein paar mal den Rechner gewechselt...

Er meinte, wir sollten gleich nur die Datensätze übernehmen die wir brauchen.

Das haben wir jetzt. Ist auch schon etwas schneller.

D.h. wir müssen jetzt nur noch die Daten anhängen:

Sheets("Liste").Select
Range("A65500").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:AQ65536").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
So machen wir das jetzt.
Das muß man aber eigentlich jetzt nur noch dadurch ersetzen, das die values in die erste freie Zeile A im Blatt Liste übertragen wird.

Gruß Lutz

Betrifft: AW: warum zur Hölle .... von: Gerd L
Geschrieben am: 19.01.2010 15:25:46

An den Jens-Lutz-Account :-)

Sheets("Liste").Range("A65536").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode= False

Vielleicht gibt 's noch ein Feedback zur anderen Frage oben?

Gruß Gerd

Betrifft: AW: warum zur Hölle .... von: Lutz
Geschrieben am: 19.01.2010 17:12:49

Hallo Gerd,

vielen Dank.

Das geht wie verrückt. Mit den beiden Verbesserungen kommen wir von ca. 10 sekunden (gefühlt eine Minute) auf nicht mal eine sekunde.

Eine Frage vielleicht noch:

Wir haben in der Firma Excel 2003 und 2007 im Einsatz.

Mit Sheets("Liste").Range("A65536").End(xlUp) verschenkt man bei Excel 2007 ja viele Zeilen. Kann man eigentlich statt End(xlup) das ganze auch mit End(xldown) machen? Gibt es das überhaupt?

Auf jeden Fall schon mal tausend Dank für Deine Hilfe

Jens und Lutz

Betrifft: AW: warum zur Hölle .... von: Uduuh
Geschrieben am: 19.01.2010 20:51:37

Hallo,
wenn A lückenlos gefüllt ist, geht es auch mit Range("A1").end(xldown).offset(1)
ansonsten
Cells(Rows.count, 1).end(xlup).offset(1)
Wenn das dann aber über 65536 Zeilen geht, hast du mit 2003 ein Problem die Datei zu öffnen.

Gruß aus’m Pott
Udo

Betrifft: AW: warum zur Hölle .... von: Lutz
Geschrieben am: 20.01.2010 12:42:07

Hallo Udo,

vielen Dank für die Mail.

Sheets("Liste").Range("A1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteValues

funktioniert leider nicht - er debuggt und xldown hat den Wert -4121.

Dafür geht:
Sheets("Liste").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

das ist zwar mit xlup aber das wird ja wohl auch mit mehr als 65536 Zeilen gehen.

Das man Excel 2003 nur bis 65536 Zeilen öffnen kann ist klar - aber Danke für den Hinweis.

Vielen Dank für Deine Hilfe

Gruß Lutz

Beiträge aus den Excel-Beispielen zum Thema "Bereich nur markieren wenn bestimmter Wert"