Excel VBA activesheet statt tabellenname?
Hallo,
ich habe ein Makro aufgezeichnet und ich möchte dass die Formeln welche ich geschrieben habe immer von der aktiven Tabelle genommen werden.
Dadurch, dass ich das Makro aufgezeichnet habe, ist mir erst später aufgefallen, dass in den Formeln in den Zellen immer der Tabellenname steht: =WENNFEHLER(SVERWEIS(30;'42297033'!A5:T5;3;);"")
Ich möchte aber, dass diese Formeln immer die Tabelle nehmen, in der das Makro ausgeführt wurde.
Im Makro steht z.B. folgendes:
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(30,'42297033'!R[3]C[-2]:R[3]C[17],3,),"""")"
'42297033' ist in dem Fall der Tabellennamen.
Noch zur Info was das gesamte Makro macht:
Ich erstelle eine neue Tabelle -> übertrage gewisse Daten von der alten Tabelle in die neue und das mit Formeln wie SVerweis() oder Wenn()
Kann ich Makro den Tabellenname durch etwas ersetzen, damit es richtig funktioniert?
Wäre schön wenn mir jemand helfen könnte, ich muss das spontan machen und bin ganz neu bei VBA
LG Benni
Hier ein vereinfachtes Beispiel:
Das ist die Ausgangstabelle mit dem Namen "test1":
Ich habe ein Makro aufgezeichnet, welches automatisch eine neue Tabelle erstellt und sobald in Spalte D "Ja" steht wird Test1 und Test2 zusammengerechnet.
So sieht dann die Zieltabelle aus:
Man sieht auch die Formel welche ich benutzt habe.
So sieht das aufgezeichnete Makro aus:
Würde ich nun dieses Makro für eine andere Tabelle benutzen wollen bzw. würde ich die Tabelle Test1 in Test2 umbenennen würde dieses Makro nicht mehr funktionieren.
Kann ich im Makro selbst durch die "suchen und ersetzen" Funktion das 'Test1' durch etwas ersetzen, welches immer auf die Ursprungstabelle zugreift?
2 Antworten
Hallo,
"=IFERROR(VLOOKUP(30,'42297033'!R[3]C[-2]:R[3]C[17],3,),"""")" ist prinzipiell nichts anderes als ein Textstring, d.h du kannst ihn auch über das verketten mehrerer Teile in dieser Art zusammenschreiben lassen:
ActiveSheet.Range("B2").Formula = "=Iferror(vlookup(30," & ActiveSheet.Name & "!" & "A5:T5, 3,),"""")"
Wobei du den Blattnamen eigentlich auch ganz weglassen könntest, wenn sich die Formel ohnehin auf das selbe Blatt beziehen soll in dem sie steht.
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(30,R[4]C[-1]:R[4]C[18],3,),"""")"
Verstehe leider nicht genau was du vorhast, aber vllt reicht es dir ja schon um das Prinzip übertragen zu können.
Ah ok also andersrum. Du kannst den Blattnamen vor Hinzufügen des neuen Blattes in einer Variable speichern um später darauf zurückzugreifen:
Sub nTab()
Dim tabName As String
tabName = ActiveSheet.Name
ActiveWorkbook.Sheets.Add After:=ActiveSheet
ActiveSheet.Range("A1").Formula = "=iferror(vlookup(30," & "'" & tabName & "'!A5:T5, 3,),"""")"
'=WENNFEHLER(SVERWEIS(30;'42297033'!A5:T5;3;);"") in englischer Schreibweise:
'=iferror(Vlookup(30,'42297033'!A5:T5,3,),"") um Variablen verwenden zu können aus Teilen zusammengesetzt:
'"=iferror(vlookup(30," & "'" & tabName & "'!A5:T5, 3,),"""")"
End Sub
Hallo,
erstmal danke für Deine Antwort.
War gerade bisschen im Stress und habe die Frage sehr kompliziert gestellt. Ich versuche es nochmal leichter zu erklären. (Deine Lösung muss ich erst ausprobieren)
Ich habe eine Tabelle (im Beispiel '42297033'), in dieser Tabelle sind gewisse Daten.
Im Makro welches ich aufgezeichnet habe, habe ich eine neue Tabelle erstellt und danach gewisse Daten von '42297033' geholt. Dafür habe ich gewisse Formeln benutzt und weil ich das Makro aufgezeichnet habe, steht in den Formeln immer der Tabellenname -> ich benötige das Makro aber für mehrere Excel Tabellen, die immer einen anderen Tabellennamen haben.
Ich habe natürlich einige Formeln und mein Ziel ist nun: in meinem Makro die Funktion "suchen und ersetzen" benutzen und '42297033' ersetzen durch etwas, dass die Daten aus der Anfangstabelle nimmt.
Was ich mir noch überlegt habe was funktionieren könnte -> Die Tabelle direkt zu Beginn umzubenennen, damit ich immer einen einheitlichen Namen benutzen kann.
Hallo
Versuch, den Tabellennamen einer Variable hinzuzufügen.
x = activesheet.name
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(30,'x'!R[3]C[-2]:R[3]C[17],3,),"""")"
Ich habe oben meine Frage durch ein Beispiel ergänzt, falls das leichter zu verstehen ist.