Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1580to1584
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

Absoluter Zellbezug mit VBA

Absoluter Zellbezug mit VBA
29.09.2017 11:21:13
Daniel
Hallo Forum,
leider komm ich selber und nach langer Suche nicht weiter.
Ich möchte ein VBA Skript schreiben das eine Index/Vergleich Formel in Zellen einer Spalte einfügt. Diese Formel beinhaltet einen absoluten Bezug auf einem anderen Tabellenblatt. Ich bekomme immer den Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler. Der Sinn und Zweck der Formel ist die Übersetzung von Kürzeln ins Text während einer Auswertung zu automatisieren.
Hier ist mein Skript:
Sub test()
Dim lz As Long
Dim i  As Long
lz = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lz
Cells(i, "C").Formula = "=INDEX(Kürzel!$B$2:$B$6;VERGLEICH(A2;Kürzel!$A$2:$A$6;0))"
Next i
End Sub
Die Excel-Datei ist unter diesem Link hochgeladen:
https://www.herber.de/bbs/user/116623.xlsx
Wenn ich einen Apostroph "'" vor dem "=" Zeichnen einfüge dann schreibt das VBA Skript die Formel in der Zelle rein. Ich habe danach versucht den Apostroph mit VBA zu entfernen, aber dann bekomme ich wieder den gleichen Fehler. Die Index/Vergleich Formeln funktioniert im Tabellenblatt einwandfrei. Was mache ich mit meinem Skript falsch?
Vielen Dank im Voraus für die Hilfe!

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

Betreff
Datum
Anwender
Anzeige
AW: Absoluter Zellbezug mit VBA
29.09.2017 11:24:32
ChrisL
Hi Daniel
Entweder verwendest du "FormulaLocal" oder besser du verwendest die englische Schreibweise:
"=INDEX(Kürzel!$B$2:$B$6,MATCH(A2,Kürzel!$A$2:$A$6,0))"
cu
Chris
AW: Absoluter Zellbezug mit VBA
29.09.2017 11:27:11
ChrisL

Sub test()
With Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
.Formula = "=INDEX(Kürzel!$B$2:$B$6,MATCH(A2,Kürzel!$A$2:$A$6,0))"
End With
End Sub
cu Chris
AW: Absoluter Zellbezug mit VBA
29.09.2017 11:31:50
Daniel
Hi
bei .Formula musst du die Formel in Englisch mit A1-Zellbezügen schreiben, dh englische Funktionsnamen und das Komma als Trennzeichen für die Parameter.
wenn du auch im Code die Formel in deutsch angeben willst, kannst du das tun mit .FormulaLocal
allerdings läuft dann dein Code nur mit einem deutschen Excel, während die .Formula-Variante in allen Sprachvarianten läuft.
Wenn du nicht weißt, wie die Formel für .formula lauten muss dann kannst du dir so behelfen:
- Schreibe die Formel normal in eine Zelle
- lasse dir im Direktfenster den Formeltext in der gewünschten Schreibweise anzeigen mit dem Befehl: ?Selection.Formula oder ?Selection.FormulaLocal
für die Programmierung ist meiner Erfahrung nach die .FormulaR1C1-Schreibweise die beste.
du brauchst auch keine Schleife
du kannst auch die Formel in einem Rutsch in alle Zellen gleichzeitig schreiben.
Range("C2:C" & lz).FormulaLocal = "=INDEX(Kürzel!$B$2:$B$6;VERGLEICH(A2;Kürzel!$A$2:$A$6;0))"

Gruß Daniel
dann wird auch dein Zellbezug auf A2 ein relativer Zellbezug.
Wenn du das mit der Schleife machst, dieser Bezug in jeder Zeile auf die Zelle A2, und ich vermute mal, dass du das nicht willst.
Anzeige
AW: Absoluter Zellbezug mit VBA
29.09.2017 11:45:45
Daniel
Vielen Dank an alle für die super schnellen Antwörte! Jeder Vorschlag hat funktioniert! Vielen Dank auch an Daniel für die Beschreibung, das Hilft mir unglaublich viel weiter VBA zu verstehen. Das mit der Übersetzungen von "," und ";" habe ich manchmal auch ohne VBA übersehen. Jetzt weiß ich wie man damit umgeht. Danke nochmals!

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige