Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1680to1684
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
Inhaltsverzeichnis

Optimierung relative Bezüge

Optimierung relative Bezüge
10.03.2019 02:41:16
Richard
Liebe Forumsmitglieder,
am Ende einer Tabelle möchte ich per Makro eine Ergebniszeile setzen, in der Summen (über die Funktion Teilergebnis, da Autofilter nutzbar sein sollen) gebildet werden.
Die Aufgabe, eine Ergebniszeile zu setzen, brauche ich vielfach, weswegen ich den Code dynamisch und damit modular aufrufbar gehalten habe.
Die Teilergebnisse brauchen einen relativen Bezug, da z.B. Teilergebnis(9;$G$2:$G$2603) die Nutzung der Autofilter aushebelt.
Die Aufgabe funktioniert mit folgenden Code (Auszug):
10 With ActiveSheet
20 For iSpalte = ersteSumme To lngletzteSpalte
30 Set rngZeile = .Range(Cells(lngletzteZeile + 1, iSpalte), Cells(lngletzteZeile + 1, iSpalte)) ' Zelle für Formeleintrag setzen
40 strFormelbezug = .Cells(2, iSpalte).Address & ":" & .Cells(lngletzteZeile, iSpalte).Address ' Adresse für Formelbezug auslesen
50 rngZeile.Formula = "=subtotal(9," & strFormelbezug & ")" ' Formel wird in Zelle geschrieben
60 rngZeile.Formula = Application.ConvertFormula(rngZeile.Formula, xlA1, xlA1, xlRelative, rngZeile) ' absolute Bezüge in Zelle in relative wandeln
70 End If
80 Next iSpalte
Die Zeile 50 schreibt eine Formel mit absoluten Bezügen [z.B. Teilergebnis(9;$G$2:$G$2603)]. Daher habe ich die Zeile 60 nach langen Recherchen in diversen Foren adaptiert und erhalte jetzt den gewünschten relativen Bezug ([z.B. Teilergebnis(9;G2:G2603)]. Eigentlich müsste es doch aber auch direkt in Zeile 40 bzw. 50 möglich sein, einen relativen Bezug zu setzen.
Könnt Ihr mir erklären, wie das geht?
Vielen Dank im Voraus

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Optimierung relative Bezüge
10.03.2019 07:13:37
Sepp
Hallo Richard,
  For iSpalte = ersteSumme To lngletzteSpalte
    Set rngZeile = .Range(.Cells(lngletzteZeile + 1, iSpalte), .Cells(lngletzteZeile + 1, iSpalte))
    rngZeile.Formula = "=subtotal(9," & .Range(.Cells(2, iSpalte), .Cells(lngletzteZeile, iSpalte)).Address(0, 0) & ")"
  Next iSpalte

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media

Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0

Wenn du deine Tabelle in eine intelligente Tabelle umwandelst, dann geht das auch ohne VBA!
 ABCDEF
1Gruß Sepp
2
3

Anzeige
oder noch kürzer
10.03.2019 07:27:11
Sepp
  For iSpalte = ersteSumme To lngletzteSpalte
    .Range(.Cells(lngletzteZeile + 1, iSpalte), .Cells(lngletzteZeile + 1, iSpalte)).Formula = _
      "=subtotal(9," & .Range(.Cells(2, iSpalte), .Cells(lngletzteZeile, iSpalte)).Address(0, 0) & ")"
  Next iSpalte

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media

Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0


 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Optimierung relative Bezüge
10.03.2019 10:18:34
fcs
Hallo Richard,
mit Address und entsprechenden Parametern bekommt auch relative Bezüge der Zelladresse.
Verwendet man FormulaR1C1, dann geht es auch direkt ohne zwischenBerechnung.
LG
Franz
10 With ActiveSheet
20 For iSpalte = ersteSumme To lngletzteSpalte
30 Set rngZeile = .Cells(lngletzteZeile + 1, iSpalte) ' Zelle für Formeleintrag setzen
40 strFormelbezug = .Range(.Cells(2, iSpalte), .Cells(lngletzteZeile, iSpalte)).Address(False,  _
False, xlA1) ' Adresse für Formelbezug auslesen
50 rngZeile.Formula = "=subtotal(9," & strFormelbezug & ")" ' Formel wird in Zelle geschrieben
70 End If
80 Next iSpalte
'kürzere Variante mit Formel in RC1-Schreibweise
10 With ActiveSheet
20 For iSpalte = ersteSumme To lngletzteSpalte
30 Set rngZeile = .Cells(lngletzteZeile + 1, iSpalte) ' Zelle für Formeleintrag setzen
50 rngZeile.FormulaR1C1 = "=SUBTOTAL(9,R[-" & (lngletzteZeile - 1) & "]C:R[-1]C)" ' Formel wird  _
in Zelle geschrieben
70 End If
80 Next iSpalte

Anzeige
AW: Optimierung relative Bezüge
10.03.2019 20:01:49
Richard
Lieber Sepp, lieber Franz,
super, vielen Dank für die beiden Lösungen, die toll funktionieren. Ich hatte vorher schon mit verschiedenen Varianten experimentiert, auch mit der R1C!-Schreibweise, bin aber immer gescheitert. Ihr habt mir jetzt zwei Lösungsansätze gezeigt, die mich weiter bringen und mein Verständnis erweitert haben.
Vielen Dank nochmals Euch beiden und ein schönes (Rest-)Wochenende.
Viele Grüße
Richard

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige