Microsoft Excel

Herbers Excel/VBA-Archiv

Variable Range in R1C1-Formel

Betrifft: Variable Range in R1C1-Formel von: Max
Geschrieben am: 27.08.2014 16:45:17

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?

  

Betrifft: AW: Variable Range in R1C1-Formel von: Daniel
Geschrieben am: 27.08.2014 17:12:38

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


  

Betrifft: AW: Variable Range in R1C1-Formel von: Max
Geschrieben am: 28.08.2014 09:50:43

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. :)


 

Beiträge aus den Excel-Beispielen zum Thema "Variable Range in R1C1-Formel"