Makro: Dynamische Formel in Zelle einfügen

[chrjh]

Hallo Leute! Ich stehe gerade vor einem ziemlich komplexen Problem: Ich habe eine große Produktkalkulation mit mehreren Tabellenblättern. 
In einem 'Haupt'blatt gibt es die Möglichkeit neue Produktvarianten hinzuzufügen. Über eine Inputbox wird eingegeben, welcher Typ neu dazu kommt. Das Wichtige ist dabei, dass die Produkte horizontal nebeneinander hinzugefügt werden und die Typen jeweils hintereinander stehen sollen. Das heißt, es kann nicht die Reihenfolge "Typ A" "Typ B" "Typ A" geben, sondern "Typ A" "Typ A" "Typ B". Das Problem habe ich bereits gelöst. 

Der knifflige Part kommt jetzt aber erst: Wenn in dem Hauptblatt neue Produkte hinzugefügt werden, sollen auf mehreren anderen Tabellenblättern ebenfalls neue Kalkulationsvorlagen eingefügt werden. Das Prinzip mit der Reihenfolge ist wieder das gleiche. Die Funktion hab ich auch bereits programmiert. Schwierig wird es jetzt aber bei der Formelverlinkung - und hier hänge ich fest:

Die neu eingefügten Kalkulationsvorlagen in den anderen Tabellenblättern, nennen wir eine davon mal "Zubehör", sollen auf neu geschaffene Zellen in dem Hauptblatt verlinken. Das ganze muss ein absoluter Bezug sein, da ja ständig neue Produkte hinzukommen können und sich somit die jeweiligen Spalten ständig verschieben.

Ich weiß wie ich per Makro Formeln in Zellen einfügen kann, nur stellt sich jetzt die Frage, wie ich die jeweiligen Spaltenbezüge dynamisch absolut verlinken kann. 

Ein einfaches Beispiel für einen Makro zum Formeleinfügen sieht ja so aus:

Sheets("MakroTest").[A1].Formula = "=B1+C1"


Da ich ja eh nur zu den jeweiligen neuen Zellen verlinken muss, dürfte das eigentlich gar nicht so schwer sein. Vor allem da auch die Zeilen konstant sind, weil es nur in die horizontale erweitert wird.

Mein Pseudocode würde also lauten:
Sheets("Zubehör").[Spalte, die neu geschaffen wurde &Zeile(?? z.B. 2)].Formula = "='Hauptblatt'![Spalte, die neu geschaffen wurde &Zeile(Zielzeile)]"

Alternativ habe ich mir das mit Cells vorgestellt:
Sheets("Zubehör").Cells(2,Columns(spaltenIndex)).Formula = "='Hauptblatt'!Cells(Zielzeile, Columns(spaltenIndexHauptblatt)"

Aus dem existierenden Programm habe ich den Spaltenindex ermittelt, in der das neue Produkt eingefügt wurde. Mein Ansatz wäre damit zu arbeiten. Es gibt ja Bezüge, wie z.B. Columns(spaltenIndex). Leider funktioniert mein Ansatz nicht. Weiß jemand einen Ansatz?

[IchBinIch]

Hi,

und was wenn Du mit einer Schleife über die Spalten läufst?

Mal so ganz ins Unreine:

Sub Test()
Dim s As Long
Dim sm As Long

With Tabelle1
    sm = .Cells(1, Columns.Count).End(xlToLeft).Column
    For s = 1 To sm
    'Der Teil wo dann die Formel eingefügt werden soll 
    Next s
End With

End Sub

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 12 - mit VBAHTML 12.6.0

Gruß
Ich

[chrjh]

und was wenn Du mit einer Schleife über die Spalten läufst?

Mal so ganz ins Unreine:

Danke schon mal für deine Antwort! Die Schlaufe müsste ich aber dann jedes Mal, wenn ich einen neuen Typ hinzufüge laufen lassen, oder verstehe ich das falsch? 
Gruß Chrjh

[IchBinIch]

Jepp.

Mir ist aber auch nicht ganz klar wie Du ermittelst wor die Spalte eingefügt wurde.

[chrjh]

Jepp.

Mir ist aber auch nicht ganz klar wie Du ermittelst wor die Spalte eingefügt wurde.

Den Spaltenindex habe ich mit einer Match-Funktion ermittelt. Ich musste ja für die vorherigen Funktionen die jeweiligen Typen anordnen. Dafür habe ich den ersten Eintrag im Blatt suchen lassen:

With ActiveWorkbook.Sheets(2)
spaltenIndex = WorksheetFunction.Match(inputEingabe, ActiveWorkbook.Sheets(2).Range("2:2"), 0)
End With

Danach habe ich mit folgender Funktion den letzten Eintrag des jeweiligen Typs suchen lassen:
Die +4 ergeben sich aus der Anzahl der Spalten, die die Breite der Vorlage vorgeben

Do Until Cells(2, spaltenIndex) <> inputEingabe
        spaltenIndex = spaltenIndex + 4
Loop

Hier werden dann erst (in diesem Fall) vier Spalten eingefügt und dann eine neue Kalkulation aus der Vorlage in diese Spalten kopiert.

Worksheets(2).Range(Columns(spaltenIndex), Columns(spaltenIndex + 3)).EntireColumn.Insert

Sheets("Vorlage").Range("B1:E300").Copy
    With Sheets(2).Range(Columns(spaltenIndex), Columns(spaltenIndex + 3))
        .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With



Die gleiche Funktion läuft dann im Prinzip auf den anderen Tabellenblättern, die gleichzeitig erweitert werden sollen. Ich komme nur nicht auf die Syntax, mit der ich in .Formula = "=irgendwas" Variablen eingeben kann. Sonst könnte ich die Bereiche, die verlinkt werden müssen perfekt ansteuern.

Edit: Dieser Code funktioniert auch ohne Probleme. Ich hab hier ein paar Zeilen ausgelassen, aber im Prinzip sollte das verständlich sein.

MfG Chrjh

[chrjh]

Ich bekomme auch bei 
sm = .Cells(1, Columns.Count).End(xlToLeft).Column 
einen "Anwendungs- oder objektdefinierten Fehler".

[IchBinIch]

Vielleicht denke ich jetzt zu einfach...

Suchst Du so was?

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1
2			5
3			4

Sub Test()
Dim spaltenindex As Long

spaltenindex = 3
With Tabelle1
    .Range("D" & spaltenindex).Formula = .Cells(2, spaltenindex) * Cells(3, spaltenindex)

End With

End Sub

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 12 - mit VBAHTML 12.6.0

Gruß
Ich

[chrjh]

Für alle die es vielleicht interessieren könnte:

Ich hab das Problem mittlerweile selber gelöst: Folgender Code funktioniert für meinen Fall genau wie ich es wollte:

Sheets(1).Cells(3, spaltenIndexZub).Formula = "='" & strKalkulation & "'!" & Sheets(2).Cells(4, spaltenIndex+ 1).Address

MfG Chrjh

[chrjh]

Vielleicht denke ich jetzt zu einfach...

Die Antwort hab ich nicht mehr gesehen. Habe eine andere Lösung für mein Problem. Vielleicht konnte ich es einfach nicht verständlich genug erklären. 
Trotzdem vielen Dank für deine Hilfe!

MfG Chrjh