Range anhand START Zeile dynamisch ermitteln?

Forumthread: Range anhand START Zeile dynamisch ermitteln?

06.12.2020 14:08:54

Selma

Hallo Leute,
ich nutze dieses Makro, um die bedingten Formatierungen per VBA zu setzen. Soweit alles in Ordnung.

Sub Bedingte_Formate()
Sheets("ToDo").Select
ActiveSheet.Cells.FormatConditions.Delete
Dim Wert As Variant
With Range("J8:J100")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
With .FormatConditions.Add(xlCellValue, xlEqual, "=""NEU""")
.Font.ColorIndex = 1
.Interior.Color = RGB(174, 209, 233)
End With
With .FormatConditions.Add(xlCellValue, xlEqual, "=""WARTEN""")
.Font.ColorIndex = 1
.Interior.Color = RGB(255, 237, 153)
End With
With .FormatConditions.Add(xlCellValue, xlEqual, "=""ERLEDIGEN""")
.Font.ColorIndex = 1
.Interior.Color = RGB(255, 106, 106)
End With
End With
'usw.....
End Sub

Ich möchte den Range-Bereich dynamisch (soll ermittelt werden) gestalten.
Ich habe im Arbeitsblatt einer Zelle den Name "START" gegeben. In dieser Zeile soll nach Zellinhalt "Status" gesucht werden. Die gefundene Zelle nehmen wir es ist J7, dann soll J7 um eine Zeile erweitert werden, dann wäre mein With Range("J8:J100") dynamisch ermittelt
Wie mache ich das bitte?
Ich denke so in etwa: Set rngFund = .Rows("START").Find(what:="Status", LookIn:=xlValues, lookat:=xlWhole)
Beste Grüße,
Selma

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Range anhand START Zeile dynamisch ermitteln?

06.12.2020 14:28:33

GerdL
AW: Range anhand START Zeile dynamisch ermitteln?

06.12.2020 15:49:06

Selma

AW: Range anhand START Zeile dynamisch ermitteln?

06.12.2020 14:28:33

GerdL

Moin Selma,
ungetestet:

Set rngStatus = Range("START").EntireRow.Find(what:="Status", LookIn:=xlValues, lookat:=xlWhole) _
If Not rngStatus Is Nothing Then
Set Bereich = Range(rngStatus.Offset(1, 0), Cells(Rows.Count, rngStatus.Column).End(xlUp))
Else
MsgBox "Fehler"
Exit Sub
End If

Gruß Gerd

AW: Range anhand START Zeile dynamisch ermitteln?

06.12.2020 15:49:06

Selma

Hallo Gerd,
es funktioniert perfekt. Vielen Dank!

Sub Bereich_definieren()
Set rngStatus = Range("START").EntireRow.Find(what:="Status", LookIn:=xlValues, lookat:=xlWhole) _
If Not rngStatus Is Nothing Then
Set Bereich = Range(rngStatus.Offset(1, 0), Cells(Range("A65536").End(xlUp).Row, rngStatus. _
Column))
MsgBox "Zelle: " & Bereich.Address(False, False)
Else
MsgBox "Fehler"
Exit Sub
End If
End Sub

Beste Grüße,
Selma

Infobox / Tutorial

Dynamische Bereichsdefinition in Excel mit VBA

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um einen dynamischen Bereich in Excel mithilfe von VBA zu definieren, folge diesen Schritten:

Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge ein neues Modul hinzu: Rechtsklicke auf "VBAProject (DeinWorkbookName)", wähle "Einfügen" und dann "Modul".
Füge den folgenden Code ein:

Sub Bereich_definieren()
    Dim rngStatus As Range
    Dim Bereich As Range

    Set rngStatus = Range("START").EntireRow.Find(what:="Status", LookIn:=xlValues, lookat:=xlWhole)

    If Not rngStatus Is Nothing Then
        Set Bereich = Range(rngStatus.Offset(1, 0), Cells(Rows.Count, rngStatus.Column).End(xlUp))
        MsgBox "Zelle: " & Bereich.Address(False, False)
    Else
        MsgBox "Fehler"
        Exit Sub
    End If
End Sub

Schließe den VBA-Editor: Drücke ALT + Q, um zum Excel-Arbeitsblatt zurückzukehren.
Führe das Makro aus: Gehe zu "Entwicklertools" > "Makros" und wähle Bereich_definieren.

Häufige Fehler und Lösungen

Fehlermeldung "Fehler": Dies passiert, wenn der gesuchte Inhalt "Status" nicht gefunden wird. Stelle sicher, dass der Inhalt korrekt geschrieben ist und sich in der Zeile mit dem Namen "START" befindet.
Makro funktioniert nicht: Überprüfe, ob die Makros in den Excel-Optionen aktiviert sind. Gehe zu "Datei" > "Optionen" > "Sicherheitscenter" und aktiviere die Makros.

Alternative Methoden

Eine alternative Methode zur dynamischen Bereichsdefinition könnte die Verwendung von Excel-Formeln sein. Du kannst die Funktion INDIREKT nutzen, um dynamische Bereiche zu generieren, jedoch ist dies nicht so flexibel wie die VBA-Lösung.

Ein Beispiel:

=INDIREKT("J" & VERGLEICH("Status";START:START;0) + 1 & ":J100")

Diese Formel nimmt den Wert aus der Zelle mit dem Namen "START" und sucht nach "Status", um den entsprechenden Bereich zu definieren.

Praktische Beispiele

Angenommen, du hast in Zelle A1 den Namen "START" und in den Zellen darunter verschiedene Statuswerte. Wenn du das Makro ausführst, wird der Bereich ab der Zelle unter "Status" bis zur letzten gefüllten Zelle in der Spalte dynamisch ermittelt und angezeigt.

Tipps für Profis

Nutze Option Explicit am Anfang deines Moduls, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
Kommentiere deinen Code besser, um später leichter nachvollziehen zu können, was jeder Abschnitt macht.
Experimentiere mit der Find-Methode, um verschiedene Suchkriterien anzuwenden, wie z.B. LookAt:=xlPart für Teilübereinstimmungen.

FAQ: Häufige Fragen

1. Frage
Kann ich das Makro für andere Spalten anpassen?
Ja, du kannst die Spaltenbezüge im Code anpassen, um den gewünschten Bereich in einer anderen Spalte zu definieren.

2. Frage
Was passiert, wenn "Status" mehrmals vorkommt?
Das Makro findet nur die erste Übereinstimmung. Du kannst die Schleifenstruktur anpassen, um alle Übereinstimmungen zu verarbeiten, falls erforderlich.

Beliebteste Forumthreads (12 Monate)

Range anhand START Zeile dynamisch ermitteln?

Forumthread: Range anhand START Zeile dynamisch ermitteln?

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Dynamische Bereichsdefinition in Excel mit VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread