Variable Range in R1C1-Formel

Forumthread: Variable Range in R1C1-Formel

27.08.2014 16:45:17

Max

Hallo alle zusammen.
Ich sitze vor einem weiteren Problem und hoffe ihr könnt mir helfen.
Ich möchte gern per VBA eine Formel in eine Zelle schreiben. Dazu nutze ich den Befehl .FormulaR1C1.
Innerhalb dieser Formel soll jedoch ein Bereich(Matrix) variabel sein.
Die komplette Codezeile:
Worksheets("Etiketten groß").Cells(18, "D").FormulaR1C1 = "=IFERROR(IF(R[-9]C[-2]=""ja"",HLOOKUP(R[-1]C[-2],Sprachen!R[-14]C[-2]:R[-6]C[1],9,FALSE)&"":"",""""),"""")"
Die Range "Sprachen!R[-14]C[-2]:R[-6]C[1]" möchte ich gern durch eine Variable ersetzen.
Die Variable wiederum ist als Range definiert und enthält den kompletten "Pfad".
Dim SprMatrix As Range
Dim rgn As Variant
rgn = Worksheets("Sprachen").Cells(1, 1).Value
Set SprMatrix = Worksheets("Sprachen").Range(rgn)
rgn = "B4:B12" = Ergebnis einer Formel auf dem Tabellenblatt "Sprachen".
Allerdings habe ich keine Ahnung, wie ich jetzt die Variable für die Range in die Formel einbinden kann.
Hat jemand evtl. nen heißen Tipp für mich?

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variable Range in R1C1-Formel

27.08.2014 17:12:38

Daniel
AW: Variable Range in R1C1-Formel

28.08.2014 09:50:43

Max

AW: Variable Range in R1C1-Formel

27.08.2014 17:12:38

Daniel

Hi
bei der Formel handelt es sich formal um einen einfachen Textstring.
um Variablen bei der Erstellung dieses Textstrings nutzen zu können, gibts zwei möglichkeiten:
a) Fixe Bestandteile und Variablen mit " & " verketten (beachte die Leerzeichen vor und nach _ dem &)


DeineVariable = "Sprachen!R[-14]C[-2]:R[-6]C[1]"
Worksheets("Etiketten groß").Cells(18, "D").FormulaR1C1 = "=IFERROR(IF(R[-9]C[-2]=""ja"",HLOOKUP(R[-1]C[-2]," & DeineVariable & ,9,FALSE)&"":"",""""),"""")"

b) den Formeltext selbst in eine Variable schreiben, aber and den Stellen, an denen die Variable eingefügt werden soll, einen eindeutigten Dummy-Text einfügen.
Dann per Replace den DummyText durch den Variablenwert ersezten:

DeineFormel = "=IFERROR(IF(R[-9]C[-2]=""ja"",HLOOKUP(R[-1]C[-2],xxx,9,FALSE)&"":"",""""),"""")"
DeineVariable = "Sprachen!R[-14]C[-2]:R[-6]C[1]"
DeineFormel = Replace(DeineFormel, "xxx", DeineVariable)
Worksheets("Etiketten groß").Cells(18, "D").FormulaR1C1 = DeineFormel

ich bevorzuge die zweite Variante, weil so die Formel im Code einen einheitlichen Text bildet und leichter zu lesen ist.
Gruß Daniel

AW: Variable Range in R1C1-Formel

28.08.2014 09:50:43

Max

Hi Daniel,
erst einmal Danke für deine Antwort.
Hab eine Nacht darüber geschlafen und meinen Fehler selber gefunden. Das es sich bei der Formel nur um einen Textstring handelt, war mir schon klar. Nur habe ich versucht die Range Sprachen!R[-14]C[-2]:R[-6]C[1] durch eine Variable mit dem Wert
"Worksheets("Sprachen").Range(rng)" zu ersetzen.
Logische Folge: Fehler 13 Typen unverträglich.
Habe jetzt auf die R1C1 Schreibweise verzichtet und den Wert der Variable zu "Sprachen!" & rng geändert.
Und was soll ich sagen: Kaum macht man's richtig, schon funktionierts. :)

Infobox / Tutorial

Variable Ranges in R1C1-Formeln mit Excel 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

Definiere die Range-Variable: Beginne mit dem Definieren deiner Range-Variable. Zum Beispiel:

Dim SprMatrix As Range
Dim rgn As String
rgn = "B4:B12" ' Beispiel für eine Range
Set SprMatrix = Worksheets("Sprachen").Range(rgn)

Erstelle den Formeltext: Du kannst die R1C1-Formel als Textstring definieren. Beispiel:

Dim DeineFormel As String
DeineFormel = "=IFERROR(IF(R[-9]C[-2]=""ja"",HLOOKUP(R[-1]C[-2],xxx,9,FALSE)&"":"",""""),"""")"

Ersetze Dummy-Text durch die Variable: Nutze die Replace-Funktion, um die Range-Variable einzufügen.
```
DeineFormel = Replace(DeineFormel, "xxx", "Sprachen!" & rgn)
```

Weise die Formel zu: Setze die Formel in die Zelle ein.

Worksheets("Etiketten groß").Cells(18, "D").FormulaR1C1 = DeineFormel

Häufige Fehler und Lösungen

Fehler 13: Typen unverträglich: Dieser Fehler tritt auf, wenn du versuchst, die Range-Variable direkt in die Formel einzufügen. Achte darauf, den Range als Textstring zu formatieren, z.B. "Sprachen!" & rgn.
Fehler bei der R1C1-Notation: Stelle sicher, dass du die R1C1-Notation korrekt verwendest. Wenn du die Range in einem anderen Format hast, musst du sie in das R1C1-Format umstellen.

Alternative Methoden

Verkettung von Strings: Du kannst die fixe Formel mit der Range-Variable verketten.

DeineVariable = "Sprachen!R[-14]C[-2]:R[-6]C[1]"
Worksheets("Etiketten groß").Cells(18, "D").FormulaR1C1 = "=IFERROR(IF(R[-9]C[-2]=""ja"",HLOOKUP(R[-1]C[-2]," & DeineVariable & ",9,FALSE)&"":"",""""),"""")"

Verwendung von Evaluate: Eine weitere Methode ist die Verwendung von Evaluate, um die Formel dynamisch zu erstellen und auszuführen.

Praktische Beispiele

Hier ist ein einfaches Beispiel, um die Verwendung von Excel VBA mit R1C1-Formeln zu veranschaulichen:

Sub Beispiel()
    Dim rgn As String
    rgn = "B4:B12"

    Worksheets("Etiketten groß").Cells(18, "D").FormulaR1C1 = "=IFERROR(IF(R[-9]C[-2]=""ja"",HLOOKUP(R[-1]C[-2],Sprachen!" & rgn & ",9,FALSE)&"":"",""""),"""")"
End Sub

In diesem Beispiel wird die Range "B4:B12" in die R1C1-Formel integriert.

Tipps für Profis

Verwende konsistente Benennungen: Achte darauf, dass die Namen der Variablen und Ranges klar und sinnvoll sind. Dies erleichtert die Wartung und das Verständnis des Codes.
Fehlerbehandlung einfügen: Implementiere Fehlerbehandlungsroutinen, um mögliche Laufzeitfehler abzufangen, besonders wenn du mit dynamischen Ranges arbeitest.

FAQ: Häufige Fragen

1. Wie kann ich die R1C1-Notation in die A1-Notation umwandeln?
Du kannst die Methode Range("A1").Formula verwenden, um die Notationen umzuwandeln.

2. Kann ich mehrere Bereiche in einer R1C1-Formel verwenden?
Ja, du kannst mehrere Bereiche definieren und sie in die R1C1-Formel einfügen, indem du sie entsprechend verkettest.

3. Was ist der Vorteil der Verwendung von R1C1-Formeln?
R1C1-Formeln sind besonders nützlich, wenn du mit variablen Bereichen arbeitest, da sie flexibler sind und sich leichter anpassen lassen, wenn sich die Struktur deines Arbeitsblatts ändert.

Beliebteste Forumthreads (12 Monate)