FormulaLocal mit variablem Zellbezug

Bild

Betrifft: FormulaLocal mit variablem Zellbezug
von: henrik r
Geschrieben am: 18.04.2015 20:31:54

Hallo zusammen,
ich möchte per VBA für Spalte B ab Zelle B1 mit der Funktion FormulaLocal folgende Formel einfügen:
=Text(A1;"0")
Ich habe bisher:
Range("B1").FormulaLocal = "=Text(A1;""0"")"
Soweit so gut. Das Problem ist aber, dass ich so immer den Zellbezug A1 einfüge. Ich möchte aber den Zellbezug variabel halten, so dass ich wenn ich mich in Zelle B2 befinden, die Formel sich auf die Zelle links neben B2 bezieht. Es soll "=Text(A2;"0")" eingefügt werden.
Wie berücksichtige ich diesen variablen Zellbezug in FormulaLocal?

Bild

Betrifft: AW: FormulaLocal mit variablem Zellbezug
von: Luschi
Geschrieben am: 18.04.2015 20:49:23
Hallo henrik
hier mal mein Vorschlag:


Sub test1()
    Dim j As Long
    For j = 1 To 100
        ActiveSheet.Range("B" & j).FormulaLocal = "=Text(A" & j & ";""0"")"
    Next j
End Sub
oder als einzelne Zeilen:
   ActiveSheet.Range("B1").FormulaLocal = "=Text(A" & Range("B1").Row & ";""0"")"
   ActiveSheet.Range("B2").FormulaLocal = "=Text(A" & Range("B2").Row & ";""0"")"
Gruß von Luschi
aus klein-Paris

Bild

Betrifft: AW: FormulaLocal mit variablem Zellbezug
von: henrik r
Geschrieben am: 18.04.2015 21:28:00
Danke für die schnelle Antwort, funktioniert perfekt!

Bild

Betrifft: AW: FormulaLocal mit variablem Zellbezug -> R1C1
von: Daniel
Geschrieben am: 20.04.2015 12:41:18
Hi
noch einfacher gehts mit R1C1- bzw Z1S1-Zellbezügen in der Formel.
diese könnten auch relatvie Zellbezüge beschreiben (in A1 müssen auch relative Zellbezüge mit einer absoluten Adresse beschrieben werden):

Range("B1").FormulaR1C1Local = "=Text(ZS(-1);""0"")"

oder
Range("B1").FormulaR1C1Local = "=Text(ZS1;""0"")"
für weitere Zeilen kannst du dann dieselbe Formel verwenden:
Range("B2").FormulaR1C1Local = "=Text(ZS(-1);""0"")"

das z steht für Zeile und das S für Spalte.
danach folgt bei absoluten Zellbezügen die Zeilen- oder Spaltennumemr,
bei relativen Zellbezügen der Versatzwert des Bezuges in Klammern, hierbei steht ein negativer wert für "nach oben" oder "nach links" und ein positiver Wert für "nach unten" oder "nach rechts".
relative Bezüge auf die gleiche Zeile oder Spalte, welches ein (0) ergeben würden, kann man wie im Beispiel gezeigt auch weglassen.
der Vorteil der Z1S1-Bezugsart ist, dass man bei relativen Zellbezügen die Adresse nicht abhängig von der Einfügezelle "berechnen" muss, sondern für alle Zellen den selben Formeltext verwenden kann.
Ausserdem wird auch die Spalte als zahl angegeben, was hilfreich ist, wenn für die formel die Spaltenbeezüge tatsächlich mal berechnet werden sollen, weil dann die Konvertierung in den Spaltenbuchctaben entfällt.
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "FormulaLocal mit variablem Zellbezug"