Variable Range in FormulaR1C1

Forumthread: Variable Range in FormulaR1C1

11.06.2020 08:19:59

Philip

Moin,
ich komme aktuell nicht weiter und habe schon viele Dinge auspprobiert. Ich möchte die Zählenwenns-Formel automatisch in die Zellen A2-G2 schreiben. Sie soll so aussehen:
Zählenwenns(B2:B77523, 3, [@[Endung]], R1C1)
Die 77523 soll jedes Mal variabel sein und daher mit lstrow eingefügt werden. Ich habe unten schon mehrer Schreibweisen ausprobiert, aber nichts davon hat geklappt. Bekomme immer den Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler.
Bisher konnte ich leider keine Hilfe zu dieser Problemstellung finden.
Freue mich, wenn jemand mir dabei weiterhelfen kann.
Dim lstrow As Long
lstrow = Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row
Range("A2").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(Range(Cells(4, 2), Cells(lstrow, 2)),3,[@[Endung]],R1C1)"
ActiveCell.FormulaR1C1 = "=COUNTIFS(R[2]C[1]:R[" & lstrow - 1 & "]C[1],3,[@[Endung]],R1C1)"
ActiveCell.FormulaR1C1 = "=COUNTIFS(B4:B" & lstrow & ",3,[@[Endung]],R1C1)"
ActiveCell.FormulaR1C1 = "=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C1)"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C2)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C3)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C4)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C5)"
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C6)"
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Cells(4, 2), Cells(lstrow, 2),3,[@[Endung]],R1C7)"

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variable Range in FormulaR1C1

11.06.2020 08:39:59

ralf_b
AW: Variable Range in FormulaR1C1

11.06.2020 09:23:59

Philip
AW: Variable Range in FormulaR1C1

11.06.2020 09:30:48

Werner
AW: Variable Range in FormulaR1C1

11.06.2020 09:35:08

ralf_b

AW: Variable Range in FormulaR1C1

11.06.2020 08:39:59

ralf_b

scheint als ob du die parameter deiner formel etwas durcheinander hast. schau dir mal die Hilfen zu den Funktionen an,
und wenn die Formel von A2 bis G2 eingetragen wird, dann hast du einen konflikt in zelle B2. dort beginnt dein Wertebereich.

AW: Variable Range in FormulaR1C1

11.06.2020 09:23:59

Philip

Ich meinte B4. Danke, habe mir die Formel nochmal mit dem Makrorecorder aufgenommen und anegschaut.
Folgender Code funktioniert:
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(R[2]C[1]:R[" & lstrow & "]C[1], 3, R[2]C[3]:R[" & lstrow & "]C[3], R[-1]C)"

AW: Variable Range in FormulaR1C1

11.06.2020 09:30:48

Werner

Hallo,
jetzt noch das Select weglassen, das braucht kein Mensch.

Range("A2").FormulaR1C1 = _
"=COUNTIFS(R[2]C[1]:R[" & lstrow & "]C[1], 3, R[2]C[3]:R[" & lstrow & "]C[3], R[-1]C)"

Gruß Werner

AW: Variable Range in FormulaR1C1

11.06.2020 09:35:08

ralf_b

mit formulalocal kannst du die deutsche bezeichnung verwenden falls du deutsches office hat.
du hattest für den zweiten Kriterienbereich einen namen verwendet. habe das mal nachgebaut bei mir.
Range("D3").FormulaLocal = "=Zählenwenns(B2:B" & lrow & " ;3;Endung[Spalte1];A1)"

Infobox / Tutorial

Variable Bereiche in FormelR1C1 effektiv nutzen

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

Variablen deklarieren: Zuerst musst Du eine Variable für die letzte Zeile deklarieren, die Du in Deiner Formel verwenden möchtest. Beispiel:
```
Dim lstrow As Long
lstrow = Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row
```
Formel einfügen: Um die Zählenwenns-Formel korrekt zu nutzen, kannst Du das ActiveCell.FormulaR1C1-Attribut verwenden. Achte darauf, die richtige Syntax zu verwenden:
```
Range("A2").FormulaR1C1 = "=COUNTIFS(R[2]C[1]:R[" & lstrow & "]C[1], 3, R[2]C[3]:R[" & lstrow & "]C[3], R[-1]C)"
```
Verwendung von Variablen: Anstelle fester Werte kannst Du Variablen verwenden, um die Formel dynamisch zu gestalten. Dies ist besonders nützlich, wenn Du mit einer großen Datenmenge arbeitest.

Häufige Fehler und Lösungen

Laufzeitfehler '1004': Dieser Fehler tritt häufig auf, wenn die Formel nicht korrekt geschrieben ist oder wenn auf einen Bereich verwiesen wird, der nicht existiert. Überprüfe, ob die Zellreferenzen in Deiner Formel korrekt sind.
Falscher Bereich: Wenn Du Zählenwenns von A2 bis G2 verwenden möchtest, stelle sicher, dass Du den Wertebereich korrekt definierst, z.B. B4:B77523.

Hier ist ein korrigierter Beispielcode:

Range("A2").FormulaR1C1 = "=COUNTIFS(R[2]C[1]:R[" & lstrow & "]C[1], 3, R[2]C[3]:R[" & lstrow & "]C[3], R[-1]C)"

Alternative Methoden

Falls Du Schwierigkeiten mit ActiveCell.FormulaR1C1 hast, kannst Du auch das Range.FormulaR1C1-Attribut verwenden, um Formeln einzufügen:

Range("A2").FormulaR1C1 = "=COUNTIFS(B4:B" & lstrow & ", 3, [@[Endung]], R1C)"

Eine weitere Alternative ist die Verwendung von FormulaLocal, wenn Du mit der deutschen Excel-Version arbeitest:

Range("D3").FormulaLocal = "=Zählenwenns(B2:B" & lstrow & ";3;Endung[Spalte1];A1)"

Praktische Beispiele

Einfaches Beispiel zur Verwendung von COUNTIFS mit variablen Bereichen:

Dim lstrow As Long
lstrow = Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row
Range("A2").FormulaR1C1 = "=COUNTIFS(R[2]C[1]:R[" & lstrow & "]C[1], 3, R[2]C[3]:R[" & lstrow & "]C[3], R[-1]C)"

Beispiel für den Einsatz von ActiveCell:

Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(Cells(4, 2), Cells(lstrow, 2), 3, [@[Endung]], R1C2)"

Tipps für Profis

Vermeide das Select-Kommando, um den Code effizienter zu gestalten. Direkte Referenzen auf Zellen sparen Zeit und machen den Code lesbarer.
Verwende die .FormulaR1C1-Methode, um mit variablen Bereichen zu arbeiten. Dies ist besonders nützlich in VBA, wenn Du dynamische Zelladressen benötigst.
Nutze Kommentare, um Deinen Code verständlicher zu machen, besonders wenn Du mit komplexen Formeln arbeitest.

FAQ: Häufige Fragen

1. Wie kann ich die letzte Zeile dynamisch in meiner Formel verwenden? Du kannst die letzte Zeile ermitteln, indem Du UsedRange.SpecialCells(xlCellTypeLastCell).Row verwendest, um flexibel mit Datenbereichen umzugehen.

2. Was ist der Unterschied zwischen FormulaR1C1 und Formula? FormulaR1C1 verwendet eine relative Referenzierung, während Formula auf die A1-Notation basiert. Das ist hilfreich, wenn Du mit variablen Bereichen in VBA arbeitest.

Durch das Verständnis dieser Konzepte kannst Du effizienter mit Excel VBA arbeiten und dynamische Formeln erstellen, die Deine Arbeit erleichtern.

Beliebteste Forumthreads (12 Monate)

Forumthread: Variable Range in FormulaR1C1

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Variable Bereiche in FormelR1C1 effektiv nutzen

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)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread