Variable in einem Index in VBA verwenden

Forumthread: Variable in einem Index in VBA verwenden

23.07.2025 12:18:15

kubden82

Hi,

ich finde meinen Fehler nicht, hier die Idee:

ich möchte per VBA eine Formel in eine Zelle schreiben, dabei soll ein Wert für eine Reihe als Variable über eine Zelle berechnet werden, damit sich diese später auch nicht in der Formel auf die Zelle bezieht sondern dann einen festen Wert aufweist.

Hier das VBA, das nicht so funktioniert, wie erhofft:

Sub Hyperlink()



        'Hyperlink zum Reiter wird eingefügt

        Dim reihe As Integer

        

        reihe = ThisWorkbook.Sheets("Auswertung").Range("B8").Value ' hier steht aktuell 4 drin, was sich aber immer weiter erhöhen wird

        'die MsgBox dient nur als Überprüfung, wird später entfernt

        MsgBox reihe

        

        ' hier das Problem, dass bei einer Änderung von B8 sich auch alle Zellen ändern, die mit der nachfolgenden Formel belegt wurden

        ActiveCell.FormulaR1C1 = _

        "=HYPERLINK(""#'""&@INDEX(Inhaltsverzeichnis,ROW(INDIRECT(""A""&VALUE(R8C2)+3)))&""'!B1"",@INDEX(Inhaltsverzeichnis,ROW(INDIRECT(""A""&VALUE(R8C2)+3))))"

        

        'hier das Problem, dass die 4 konstant ist, sich aber bei jeder Ausführung um 1 erhöhen wird

        'ActiveCell.FormulaR1C1 = _

        "=HYPERLINK(""#'""&@INDEX(Inhaltsverzeichnis,@ROW(INDIRECT(""A""&4+3)))&""'!B1"",@INDEX(Inhaltsverzeichnis,@ROW(INDIRECT(""A""&4+3))))"



        'hier das Problem, dass in der Formel in der der Zelle dann später @reihe steht und somit nicht berechnet werden kann

        'ActiveCell.FormulaR1C1 = _

        "=HYPERLINK(""#'""&@INDEX(Inhaltsverzeichnis,@ROW(INDIRECT(""A""&reihe)))&""'!B1"",@INDEX(Inhaltsverzeichnis,@ROW(INDIRECT(""A""&reihe))))"

    

End Sub

Ich hoffe, ihr könnt mir helfen, habe keine Lust die Links immer manuell anzupassen :-(, danke

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variable in einem Index in VBA verwenden

23.07.2025 12:32:23

daniel

Hi

wenn du einen Text aus festem Text und Variablen zusammen setzen willst, gibt es zwei Möglichkeiten:

a) Verketten:
hierbei muss beachtet werden, dass der Feste Text immer mit einem Anführungszeichen beendet bzw wieder gestartet werden muss.
Es wird manchmal etwas unübersichtlich, wenn an der Grenze Anführungzeichen stehen, die zum Text gehören und daher gedoppelt sind, dann muss man genau zählen.

NeuerText = "FesterText Teil 1 " & Variable & " Fester Text Teil 2"

b) Austauschen eines Dummy-Textes:
diese Variante ist etwas übersichtlicher bei Langen Texten und wenn die Variable mehrfach in den Text eingefügt werden muss.

NeuerText = Replace("Fester Text Teil 1 xxx Fester Text Teil 2", "xxx", Variable)

Gruß Daniel

AW: Variable in einem Index in VBA verwenden

23.07.2025 12:58:20

kubden82

Danke, die Idee hatte ich ja mit der Variablen reihe auch und mit deinem NeuerText wird auch das richtige in NeuerText (hier A7), aber das weitere Verarbeiten im Index bekomme ich nicht hin.

Sub Hyperlink()



        'Hyperlink zum Reiter wird eingefügt

        Dim reihe As Integer

        Dim NeuerText As String

        

        reihe = ThisWorkbook.Sheets("Auswertung").Range("B8").Value + 3 ' hier steht aktuell 7 drin, was sich aber immer weiter erhöhen wird

        'die MsgBox dient nur als Überprüfung, wird später entfernt

        MsgBox reihe

        

        NeuerText = "A" & reihe

        MsgBox NeuerText

        

        'Problem: die Formel wird dann in der Zelle so eingetragen: =HYPERLINK("#'"&@INDEX(Inhaltsverzeichnis;@neuerText)&"'!B1";@INDEX(Inhaltsverzeichnis;@neuerText))

        ActiveCell.FormulaR1C1 = _

        "=HYPERLINK(""#'""&@INDEX(Inhaltsverzeichnis,NeuerText)&""'!B1"",@INDEX(Inhaltsverzeichnis,NeuerText))"

        

End Sub

In der Formel in der Zelle auf dem Arbeitsblatt steht nämlich dann nachdem ich das VBA ausgeführt habe @neuerText und nicht A4

AW: Variable in einem Index in VBA verwenden

23.07.2025 13:13:07

daniel

ich hab dir doch beschreiben, was du machen musst.
bei mir heißt es "Variable" und bei dir "NeuerText".

bekommst du die Umsetzung von dem, was ich beschrieben habe, nicht alleine hin?
habe ich es zu kompliziert formuliert?

Gruß Daniel

AW: Variable in einem Index in VBA verwenden

23.07.2025 13:26:47

kubden82

sorry, wahrscheinlich denke ich zu kompliziert

deine Variable ist bei mit reihe und wird wie bei dir in NeuerText eingefügt

reihe = ThisWorkbook.Sheets("Auswertung").Range("B8").Value + 3

NeuerText = "A" & reihe

Die "" passen, da es ja später als Zelle A7 heißen soll.

NeuerText gibt auch den richtigen Wert aus (A7), daher lass ich mir ja die MsgBox anzeigen, aber die Übernahme in ActiveCell.FormulaR1C1 funktioniert bie mir nicht bzw. ich weiß einfach nicht, was ich dort nach Inhaltsverzeichnis,.... eintragen soll

soetwas INDEX(Inhaltsverzeichnis,ROW(R[-9]C[-1])) funktioniert halt nicht, da sich die -9 ändern muss

vielleicht kannst du nochmal versuchen es mir zu erklären, danke

AW: Variable in einem Index in VBA verwenden

23.07.2025 14:18:05

daniel

warum soll das mit -9 nicht genauso funktionieren?

du musst dir den Wert berechnen und dann in dein Text einfügen:

x = -9

..."INDEX(Inhaltsverzeichnis,ROW(R[" & x & "]C[-1]))"

Gruß Daniel

AW: Variable in einem Index in VBA verwenden

23.07.2025 14:50:58

kubden82

leider müsste die -9 immer um -5 pro Durchgang (also die Zahl, die in B8 steht) des VBA erhöht werden ...

ich denke, ich werde mal versuchen anders an das Problem ran zu gehen bzw. mit deinen Vorschlägen an die Lösung ran zu kommen bzw. mein Problem genauer beschreiben

vielen Dank für deine Hilfe und Denkanstöße

AW: Variable in einem Index in VBA verwenden

23.07.2025 15:00:00

daniel

naja, du musst dann die Variable pro Durchgang neu berechnen.
Aber das war nicht Gegenstand deiner Frage.

Wie verändert sich denn die Variable pro durchgang?
oder kannst du das vermeiden, wenn du hier einfach den absoluten Zellbezug auf die Zelle setzt statt dem relativen?
wenn du dich immer auf die Zelle A7 beziehen willst, dann ist das in R1C1: "R7C1"

Gruß Daniel

AW: Variable in einem Index in VBA verwenden

23.07.2025 15:18:56

kubden82

ich habe auf einem Reiter eine Auflistung aller Reiternamen (Inhaltsverzeichnis) und immer wenn ein neuer Reiter dazu kommt, wird auf dem Reiter Auswertung ein Baustein in die erste freie Reihe gesetzt, der insgesamt 5 Reihen breit ist

der Index soll also auf die richtige Stelle im Inhaltsverzeichnis verweisen, diese geht pro Durchgang um eine Reihe nach unten (also Zahl +1) und darf nach dem VBA keine Formel mit einer Variablen mehr enthalten, sonst würde sich der Link ja auch bei jedem Durchgang neu anpassen

ich werde das mal mit dem Zellbezug versuchen, da ich ja weiß, wie viele Reiter schon dran sind und damit dann die richtige Stelle berechnen, danke für die verschiedenen Ansätze

AW: Variable in einem Index in VBA verwenden

23.07.2025 15:41:51

kubden82

deine Idee mit feste Reihe hat mich darauf gebracht, einfach eine Rechenformel als Variable zu nehmen und jetzt läuft es, danke nochmal

        differenz = -4 * (reihe - 1) - 9

        

        ActiveCell.FormulaR1C1 = _

        "=HYPERLINK(""#'""&@INDEX(Inhaltsverzeichnis,ROW(R[" & differenz & "]C[-1]))&""'!B1"",@INDEX(Inhaltsverzeichnis,ROW(R[" & differenz & "]C[-1])))"

Variable in einem Index in VBA verwenden

Forumthread: Variable in einem Index in VBA verwenden

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Variable in einem Index in VBA verwenden

Forumthread: Variable in einem Index in VBA verwenden

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread