Zellen markieren mit relativen Verweisen

18.07.2025 19:14:25

hans

Hallo liebe Excel-Freunde,

heute wende ich mich mit folgendem Anliegen an Euch:

Erklärung:
Ich habe eine Tabelle (Mittelwerte). In der Tabelle werden für 3 verschiedene Gruppen (Kindergärten, Offene Ganztagsschulen, Gesamtschule) Essenmengen pro Person eingetragen. Und das Ganze für 3 verschiedene Menülinien (Menü 1, Menü 2, Vegetarisch).
Eine andere Tabelle (Produktionsplanung) greift darauf zu und holt sich die Werte von den einzelnen Kunden bzw. Kundengruppen. Wegen verschiedener Altersstufen sind die Portionsmengen pro Person unterschiedlich.

Beispiel: Ein Kita-Kind bekommt 160 g. Kartoffeln, ein Schulkind bekommt 200 g. Kartoffeln.

Die Essenmengen sind auch noch in 4 verschiedene Kategorien (Spalte 1 - 4 ) unterteilt.

Je nach Speiseplan müssen in dieser Mengentabelle die Mengen angepasst bzw. verändert werden. Z.B. gibt es 4 kleine Fleischbällchen pro Person, aber nur eine Bratwurst pro Person. Dazu wird meistens die Spalte 4 genutzt. (Dann muss dort eine 4 oder 1 eingetragen werden.

Und das Ganze ist manuell recht mühsam, denn ich muss jeden Kunden bzw. jede Spalte manuell anklicken und markieren und dann die Zahlen verändern.

Nun habe ich Makros erstellt, die mir 1. die Markierarbeit abnehmen und zweitens nach einer Abfrage auch die Zahl ändern.

Dazu gibt es verschiedene Buttons...

K = Kitas (markiert alle Zellen ab Startzelle für die Kitas) (Button in Zelle i1)
O = Offene Ganztagsschulen (markiert alle OGS ab Startzelle für alle OGS) (Button in Zelle J1)
P = Gesamtschule (markiert alle Gesamtschul-Zellen ab Startzelle für die Gesamtschule), (Button in Zelle K1)
A = Soll alle Zellen markieren ab Startpunkt für alle 3 Gruppen. (Button in Zelle M1)

"Zahlabfrage" = Zahl wird abgefragt und automatisch in alle markierten Zellen eingetragen. (Button in Zelle H2).
(Zum Testen am besten einmal den Cursor in die Zelle L7, dann den Button "K" (in Zelle i1) drücken, dann sind alle Zellen (L7, S7, Z7, AG7 AN7, L24, S24, Z24, AG24, AN 24, L41, S41, Z41, AG41, AN41) markiert.

Dann den Button "Zahl abfrage" (in Zelle H2) betätigen und z.B. eine 3 eintragen. Dann wird in jede markierte Zelle eine 3 eingetragen (bedeutet, jedes Kita-Kind bekommt 3 Stücke der Komponente).

"K, O, P" klappt ohne Probleme
Aber "A" (Alle) bekomme ich nicht hin. Dann kommen Fehlermeldungen (bei absoluten Bezügen klappt es, aber bei relativen Bezügen klappt es leider nicht).

Kann mir in der Angelegenheit jemand helfen bzw. Tipps geben, wie es klappen könnte.

Herzlichen Dank für Eure Unterstützung.

Gruß

Hans

Beispiel-Datei:

https://www.herber.de/bbs/user/178117.xlsm

AW: Zellen markieren mit relativen Verweisen

18.07.2025 19:37:01

daniel

Hi
fehlermeldungen bekomme ich auch bei Button A nicht.
da werden halt immer die selben Zellen markiert.
das liegt daran, dass du für den Button A Ausgangszelle für die Zellbezüge fest definiert hast:

 Set Start = ws.Range("A1") ' Dein Startpunkt

...

 Set cell = Start.Offset(Offsets(i)(0), Offsets(i)(1))

alle Offsets gehen immer von A1 aus.

für die anderen Buttons ist die Ausgangszelle die jeweils aktive Zellen

ActiveCell.Range( _

        "A1,H1,O1,V1,AC1,AJ1,AQ1,A18,H18,O18,V18,AC18,AJ18,AQ18,A35,H35,O35,V35,AC35,AJ35,AQ35" _

        ).Select

macht das den unterschied?
vielleicht musst du für A die Startzelle anders definieren.

Gruß Daniel

AW: Zellen markieren mit relativen Verweisen

20.07.2025 21:23:15

Eifeljoi 5

Hallo

So wie ich das ganze sehe(kann mich auch täuschen) könnte man das ganze auch eventuell ohne Zellformel und ohne VBA machen, dies nur als Tipp.

AW: Zellen markieren mit relativen Verweisen

21.07.2025 20:12:16

daniel

Hi Eifeljoi 5
könntest du deinen Tip mal konkretisieren?
ich hab keine Ahnung was du meinst.
Gruß Daniel

AW: Zellen markieren mit relativen Verweisen

19.07.2025 17:50:47

Hans

Hallo Daniel,

danke für Deine Rückmeldung.
In meiner Beispieldatei waren bei den drei funktionierenden Beispielen (Kitas, OGS, PWG) die Makros mit relativen Bezügen enthalten.

Bei "Alle" war das Makro mit absoluten Bezügen hinterlegt.

Heute habe ich nochmal per Makro-Rekorder den Weg aufgezeichnet (jede einzelne Zelle markieren), vorher "Relative Verweise verwenden" aktiviert.

Da ist dann ein Fehler im Makro, woraus ich nicht schlau werde.

Vielleicht hast Du dazu noch eine Idee!.

Danke dir, schönen Samstag noch,

Hans

Sub AlleZellenMarkierenNeu()

'

' AlleZellenMarkierenNeu Makro

'



     ActiveCell.Offset(-15, -34).Range( _

        "A1,H1,O1,V1,AC1,A18,H18,O18,V18,AC18,A35,H35,O35,V35,AC35,A53,H53,O53,V53,AC53,AJ53,AQ53" _

        ).Select

    ActiveCell.Offset(37, 8).Range("A1").Activate

    

    ActiveCell.Offset(-52, -42).Union(Range( _

        "V87,AC87,AJ87,AQ87,A106,A122,A1,H1,O1,V1,AC1,A18,H18,O18,V18,AC18,A35,H35,O35,V35,AC35,A53,H53,O53,V53,AC53,AJ53,AQ53,A70,H70,O70,V70" _

        ), Range("AC70,AJ70,AQ70,A87,H87,O87")).Select

    ActiveCell.Offset(69, -42).Range("A1").Activate

    

    ActiveCell.Offset(-121, 0).Union(Range( _

        "V87,AC87,AJ87,AQ87,A106,A122,A138,A1,H1,O1,V1,AC1,A18,H18,O18,V18,AC18,A35,H35,O35,V35,AC35,A53,H53,O53,V53,AC53,AJ53,AQ53,A70,H70,O70" _

        ), Range("V70,AC70,AJ70,AQ70,A87,H87,O87")).Select

    ActiveCell.Offset(16, 0).Range("A1").Activate

    

End Sub

Folgende Zeile wird im VBA Editor gelb hinterlegt:

ActiveCell.Offset(-15, -34).Range( _
"A1,H1,O1,V1,AC1,A18,H18,O18,V18,AC18,A35,H35,O35,V35,AC35,A53,H53,O53,V53,AC53,AJ53,AQ53" _
).Select

AW: Zellen markieren mit relativen Verweisen

19.07.2025 18:07:30

Daniel

ActiveCell.Offset(-15, -34)

Das Offset erfordert; dass beim Auslösen dieses Makros eine Zelle Aktiv sein muss, die in Zeile 16 oder unterhalb und in Spalte AI oder rechts davon steht.

Hast du das berücksichtigt?

AW: Zellen markieren mit relativen Verweisen

19.07.2025 20:16:40

Hans

Hallo Daniel,

mit dem vorigen Code bin ich nicht weitergekommen. Nach der zu markierenden Zelle sollte dann eine weitere markiert werden usw.

Ich habe ChatGPT mal bemüht, einen Code zu erstellen, der im ersten Step gar nicht so schlecht ist.

Aber:
1. Es werden auch Zellen markiert (ab Spalte AR, weil wahrscheinlich bei den OGS zwei Kunden mehr sind (Lünern und Lünern 2)
Das wäre nicht so tragisch, weil die Zellen von der anderen Tabelle nicht abgefragt werden (könnte ich zur Not die Schriftfarbe auf "Weiß" setzen, dann fällt es nicht auf, dass dort Einträge sind. Oder Makro erweitern, dass am Ende die Zellen wieder geleert werden.

2. Was aber nicht klappt ist, wenn ich z.B. ab der Spalte K etwas ändern möchte. Die Spalte L scheint wieder absolut zu sein.

Hast Du dafür eine Lösung. Hab schon mehrere Sachen probiert, aber komme nicht weiter.

Danke

Sub MarkiereRelativeZellen()

    Dim basisZelle As Range

    Dim zielZellen As Range

    Dim zeilenOffsets As Variant

    Dim spalten As Variant

    Dim zeileOffset As Variant

    Dim spalte As Variant



    Set basisZelle = ActiveCell  ' Startpunkt



    ' Zeilen-Offsets relativ zur aktiven Zelle (entsprechend Zeilen 7, 24, 41, ...)

    zeilenOffsets = Array(0, 17, 34, 52, 69, 86, 105, 121, 137)



    ' Spalten, die in den meisten Zeilen vorkommen

    spalten = Array("L", "S", "Z", "AG", "AN", "AU", "BB")



    ' Durchlaufe alle Kombinationen

    For Each zeileOffset In zeilenOffsets

        For Each spalte In spalten

            On Error Resume Next ' Für Zellen, die evtl. nicht vorkommen (z. B. AU in Zeile 7)

            If zielZellen Is Nothing Then

                Set zielZellen = basisZelle.Offset(zeileOffset, Columns(spalte).Column - basisZelle.Column)

            Else

                Set zielZellen = Union(zielZellen, basisZelle.Offset(zeileOffset, Columns(spalte).Column - basisZelle.Column))

            End If

            On Error GoTo 0

        Next spalte

    Next zeileOffset



    ' Zusätzliche Einzelzellen (L112, L128, L144)

    Set zielZellen = Union(zielZellen, _

        basisZelle.Offset(112 - basisZelle.Row, Columns("L").Column - basisZelle.Column), _

        basisZelle.Offset(128 - basisZelle.Row, Columns("L").Column - basisZelle.Column), _

        basisZelle.Offset(144 - basisZelle.Row, Columns("L").Column - basisZelle.Column) _

    )



    ' Markieren

    zielZellen.Select

End Sub

AW: Zellen markieren mit relativen Verweisen

19.07.2025 21:01:09

Daniel

Hast du schon mal probiert, uns einfach zu erklären was du machen willst?

Aus einem nicht funktionierenden Makro eines unerfahrenen Programmieren rückzuübersetzen, was dieser eigentlich wollte ist nicht ganz so einfach.

AW: Zellen markieren mit relativen Verweisen

20.07.2025 12:01:25

Hans

Hallo Daniel,

ja gerne erkläre ich nochmal, was ich machen möchte.

Im Anhang nochmals die Tabelle.

Ich möchte den Cursor z.B. In Zelle L7 setzen, dann den Button "N" drücken (steht in N1). Dann sollen alle vorher definierten Zellen ab L7 markiert sein.

L7,L24,L41,L59,L76,L93,L112,L128,L144,
S7,S24,S41,S59,S76,S93,
Z7,Z24,Z41,Z59,Z76,Z93,
AG7,AG24,AG41,AG59,AG76,AG93,
AN7,AN24,AN41,AN59,AN76,AN93,
AU59,AU76,AU93,
BB59,BB76,BB93,

Das klappt auch soweit.

Wenn ich den Cursor aber z.B. in i7 setze, sollen ab der Zelle i7 alle relevanten Zellen markiert sein.
Das klappt nicht. Der Cursor springt immer in die Spalte L. Der Bereich soll aber ab i4 beginnen.

Den Durchblick in einem fremden Code zu haben, ist sicherlich erst mal schwierig. Das verstehe ich.

Aber Du mit deinem Fachwissen findest vielleicht die Stelle, dass der Bereich nicht in der Spalte L beginnt, sondern schon in der Spalte i.
Auf den Buttons "K, O, P" klappt alles, nur wenn ich alle Zellen für alle Kunden markieren möchte, klappt es leider nicht.

Hat wahrscheinlich was mit den Offset zu tun, das versteh ich aber nicht.

https://www.herber.de/bbs/user/178119.xlsm

Gruß
Hans

AW: Zellen markieren mit relativen Verweisen

20.07.2025 15:16:32

Daniel

Erstmal ist dein Fachwissen gefragt:
Wann ist eine Zelle "relevant"?

AW: Zellen markieren mit relativen Verweisen

20.07.2025 20:19:24

Hans

Die relevanten Zellen ergeben sich aus der Startzelle, die in i4 beginnen kann (Cursor muss vor Start des Makros in die gewünschte Startzelle gesetzt werden).

Wenn die Startzelle z.B. J4 ist, sollen die Zellen jeweils auch in der nächsten Spalte beim nächsten Kunden markiert sein, also eine Spalte weiter wandern (Q4,Y4...usw.) aber in den gleichen Abständen

Wenn die Startzelle z.B. i15 ist, sollen die weiteren Zellen in einer Spalte beim nächsten Kunden weiter markiert werden (P15, W15, AD15, AK154)

Start des Tabellenbereiches ist die Zelle i4, = 1. Kunde, 1. mögliche Zelle.

Dann kommen jeweils die Zellen (Aus den Kitakunden)
Menü 1: I4, P4, W4, AD4, AK4
Menü 2: i21, P21,W21, AD21, AK21
Vegetarisch: i38, P38,W38,AD38,AK38

Dann kommen die OGS Kunden:

Menü 1: i56, P56, W56, AD56, AK56,AR56, AY56
Menü 2: i73, P73, W73, AD73, AK73,AR73, AY73
Vegetarisch: i90, P90, W90, AD90, AK90,AR90, AY90

Dann kommt die PWG-Schule

Menü 1: i109,
Menü 2: i125,
Vegetarisch: i141,

Das Makro auf Button K markiert nur die Zellen der Kitakunden
Das Makro auf Button O markiert nur die OGS Kunden
Das Makro auf Button P markiert nur die PWG Schule
Das Makro auf N Soll aber die Zellen aller Kunden auf einmal markieren (ab Startzelle mit den genannten Abständen).

Ich hoffe, dass es nun klarer ist.
Ansonsten gerne nochmal das Makro nur Kitas starten
Dazu einmal testweise Zelle i4 anwählen, dann Makro auf Button K starten. Dann werden die weiteren Zellen in den Spalten der nächsten Kunden markiert.
Dann testweise die Startzelle L7 wählen, Makro auf Button K starten. Dann werden jeweils die Zellen in den Spalten der nächsten Kunden markiert.

AW: Zellen markieren mit relativen Verweisen

21.07.2025 20:09:47

daniel

Hi
ist mir zu kompliziert, das was du da gebastelt hast.

ich würde das so machen:

Sub Selektieren()



Dim Pos As Long

Dim Zelle As Range

Dim Menü As String

Dim Bereich As Range



Dim Gesamtbereich As Range



'hier nach gewünschten Ort (Kita, usw) die Zeilen anpassen,

Set Gesamtbereich = Rows("1:154").Resize(, ActiveSheet.UsedRange.Columns.Count).Offset(0, 7)





'Spalte bestimmen

For i = 1 To 4

    If VarType(ActiveCell.Offset(0, -i).Value) = vbString Then Exit For

Next



If i  5 Then

    Menü = ActiveCell.Offset(0, -i)

    For Each Zelle In Gesamtbereich.Cells

        Debug.Print Menü, Zelle.Text

        If Zelle.Text = Menü Then

            If Bereich Is Nothing Then

                Set Bereich = Zelle.Offset(0, i)

            Else

                Set Bereich = Union(Bereich, Zelle.Offset(0, i))

            End If

        End If

    Next

    

    Bereich.Select

End If

        



End Sub

in der beschriebenen Zeile passt du den Code dann an den Bereich an in dem du suchen willst.
alle: .Rows("1:154")
Kitas: .Rows("1:51")
usw.

Gruß Daniel

AW: Zellen markieren mit relativen Verweisen

23.07.2025 19:50:48

Hans

Hallo Daniel,

prima, danke,

Heute hatte ich endlich Zeit, Deinen Vorschlag zu testen.

Läuft genau so wie ich es mir vorgestellt habe.

Hast mir damit sehr geholfen.

Viele Grüße

Hans

Zellen markieren mit relativen Verweisen

Forumthread: Zellen markieren mit relativen Verweisen

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Zellen markieren mit relativen Verweisen

Forumthread: Zellen markieren mit relativen Verweisen

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread