Tabellenblatt-Name als Variable und Formel

[Dude85]

Hallo miteinander

In meinem Excel gibt es das Tabellenblatt mit dem Namen "PB". Im Hintergrund habe ich in einem Makro folgenden Code & Formel:

Code:

   For lngIndex = 0 To UBound(var0)
      .Cells(lngRow, lngIndex + 2) = ActiveWorkbook.Worksheets("PB").Range(var0(lngIndex)).Value
   Next

End With

....
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(PB!R5C30,""-"",PB!R5C55,""-"",PB!R5C63,""_"",PB!R2C61)"

Natürlich funktioniert das nicht mehr, wenn der Tabellenblatt-Name durch den User geändert wird.
Ich habe versucht den Tabellenblattname als Variable zu definieren. Es will nicht funktionieren, Laufzeitfehler 1004, 538 usw.

Wie muss ich die Variable definieren und in der Formel einfügen?

Ich danke euch für die Hilfe!
LG Urs

[Der Steuerfuzzi]

Hallo,

das Tabellenblatt hat auch einen "internen" Namen, über den man es unabhängig vom angezeigten Namen ansprechen kann. Dieser Name steht in der VBE hinter dem Tabellennamen in Klammern.

Standardmäßig hat z. B. das erste Tabellenblatt in einer deutschen Excel-Version den internen Namen "Tabelle1". Diese kannst Du z. B. so als Variable benutzen:

Code:

Dim MeineTabelle as Worksheet
Set MeineTabelle = Tabelle1

[Rabe]

Hi Urs,

Wie muss ich die Variable definieren und in der Formel einfügen?

Mit dieser Formel
=TEIL(ZELLE("filename";$A$1);FINDEN("]";ZELLE("filename";$A$1))+1;FINDEN("]";ZELLE("filename";$A$1))-FINDEN("[";ZELLE("filename";$A$1))-1)
in einer Zelle (z.B. M3) kannst Du den Blattnamen in diese Zelle schreiben lassen und dann im Makro einer Variablen zuweisen.

Dim BlattName as String
BlattName = Range("M3").Value

Dann ist es universell!

Arbeitsblatt mit dem Namen 'Tabelle2'
	L	M
1	Pfad & Dateiname:	C:\lokale Daten\Excel\Forum\2017\2017-KW49\[Vergleich Stückliste.xlsb]Tabelle2
2	Dateiname:	Vergleich Stückliste.xlsb
3	dieses Blatt	Tabelle2

Zelle	Formel
M1	=ZELLE("filename")
M2	=TEIL(ZELLE("filename";$A$1);FINDEN("[";ZELLE("filename";$A$1))+1;FINDEN("]";ZELLE("filename";$A$1))-FINDEN("[";ZELLE("filename";$A$1))-1)
M3	=TEIL(ZELLE("filename";$A$1);FINDEN("]";ZELLE("filename";$A$1))+1;FINDEN("]";ZELLE("filename";$A$1))-FINDEN("[";ZELLE("filename";$A$1))-1)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Dude85]

Hallo Freunde!

Ich muss es morgen ausprobieren, bin nicht dazu gekommen.
Melde mich wieder - Danke für die beiden Inputs!!!!

Alles Gute, Urs

[Der Steuerfuzzi]

Ergänzung: Mit Tabelle1.Name oder bei Verwendung der Variable mit MeineTabelle.Name kannst Du den Namen "auslesen" und damit die Formel "zusammenbauen".

Aber ich würde den Tabellennamen in einfach Anführungsstriche setzen, da ein möglicher neuer Name z. B. Leerzeichen enthalten könnte.

Also z. B.:

Code:

ActiveCell.FormulaR1C1 = _
        "=CONCATENATE('"&Tabelle1.Name&"'!R5C30,""-"","&Tabelle1.Name&"'!R5C55, usw.

[Dude85]

Lieber Michael

Dein Tipp ist ganz hilfreich!
Ich habe es immer ohne .Name versucht. Mit deiner Ergänzung klappt es nun! *happy*

Dankeschön und eine gute Zeit
LG Urs