Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 14:59:21
Zellformel in VBA
23.04.2024 14:47:39
Formel SVERWEIS
23.04.2024 14:27:08
Excel - Zellen verketten anhand anderer Spalte
23.04.2024 14:23:45
Gehe zu Tabellenblatt
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1128to1132
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1128to1132
1128to1132
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bereich nur markieren wenn bestimmter Wert | Herbers Excel-Forum

Bereich nur markieren wenn bestimmter Wert
19.01.2010 12:34:07
jens

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

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bereich nur markieren wenn bestimmter Wert
19.01.2010 12:45:02
Thomas Hohenester
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
AW: Bereich nur markieren wenn bestimmter Wert
19.01.2010 12:58:16
Lutz
Hallo Thomas,
vielen Dank, ich brauche es in einem Makro weil danach noch mehr kommt.
Gruß Lutz
Application.Match
19.01.2010 12:46:37
Matthias5
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
Anzeige
AW: Application.Match
19.01.2010 13:01:54
jens
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
AW: Application.Match
19.01.2010 13:11:25
Matthias5
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
Anzeige
AW: Application.Match
19.01.2010 13:31:22
Gerd L
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
AW: Application.Match
19.01.2010 14:14:22
jens
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
Anzeige
AW: Bereich nur markieren wenn bestimmter Wert
19.01.2010 12:53:44
Helge Kuehn
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
AW: Ende nach Formellogik
19.01.2010 14:26:16
Gerd L
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
Anzeige
AW: Ende nach Formellogik
19.01.2010 16:52:32
Lutz
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
AW: Ende nach Formellogik
19.01.2010 17:27:11
Gerd L
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
Anzeige
AW: Ende nach Formellogik
19.01.2010 18:15:52
Lutz
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
Anzeige
AW: Ende nach Formellogik
19.01.2010 19:36:19
Gerd L
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
AW: Ende nach Formellogik
20.01.2010 13:26:35
Lutz
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
Anzeige
AW: Ende nach Formellogik
20.01.2010 22:00:31
Gerd L
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
AW: Ende nach Formellogik
20.01.2010 23:39:23
Lutz
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
Anzeige
AW: Ende nach Formellogik
23.01.2010 08:54:10
Hajo_Zi
Hallo Lutz,
warum ist der Beitrag als offen gekennzeichnet. Ich kann keinen Grund in Deinem Beitrag sehen.

AW: Ende nach Formellogik
23.01.2010 12:45:16
Lutz
Hallo Hajo,
der Thread ist natürlich erledigt - sorry.
Gruß Lutz
warum zur Hölle ....
19.01.2010 14:29:51
Rudi Maintaire
machst du mehrere Threads unter verschiedenen Namen auf, Jens-Lutz-Lars?
Gruß
Rudi
AW: warum zur Hölle ....
19.01.2010 15:12:01
Lutz
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
Anzeige
AW: warum zur Hölle ....
19.01.2010 15:25:46
Gerd L
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
AW: warum zur Hölle ....
19.01.2010 17:12:49
Lutz
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
AW: warum zur Hölle ....
19.01.2010 20:51:37
Uduuh
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

AW: warum zur Hölle ....
20.01.2010 12:42:07
Lutz
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

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige