Per VBA Formeln einfügen

[diving_excel]

Hallo zusammen!

über einen VBA Code möchte ich in den Zeilen 1-100 in Spalte 24 Formeln einfügen. Diese soll sich an die jeweilige Zeile anpassen (deshalb das "i" in der Formel) und wird nun mittels Verkettungen eingebracht, aber leider nimmt er das nicht an. Wisst ihr wo mein Fehler liegt?

Code:

Private Sub CommandButton1_Click()
Dim i As Long
For i = 1 To 100
With Tabelle1
    If Worksheets("Tabelle1").Cells(i, 3).Value = "Kosten" Then
       Worksheets("Tabelle1").Cells(i, 24).Formula =
      "=IF(X$30<=TODAY(),$G$19*SUMIFS('Tabelle2'!$AC:$AC,'Tabelle2'!$AD:$AD,""0"",'Tabelle2'!$Y:$Y,LEFT('Tabelle1'!$F" & i & ",7)&""_""&'Tabelle1'!X$32),$G$19*SUMIFS('Tabelle3'!S:S,'Tabelle3'!$E:$E,LEFT('Tabelle1'!$F" & "i,7)&""_Costs"",'Tabelle3'!$J:$J,""Latest""))"
    End If 
End With
  Next i
End Sub

Hinweis: der Zeilenumbruch in der Formel ist im VBA Code nicht gegeben und wird hier nur der Aufgrund der Übersichtlichkeit so dargestellt.
Danke und Gruß!

[Klaus-Dieter]

Hallo,

warum schreibst du deine gewünschte Formel nicht einfach in Zelle X1 und ziehst sie dann bis Zeile 100 nach unten?

Welche Formel soll denn eingetragen werden. Da dein Quelltext auf einen Fehler läuft, wird sie nicht eingetragen, habe keine Lust, das zu analysieren.

[diving_excel]

Die Formeln unterscheiden sich je nach Wert in Spalte 3: Hier im geposteten Beispiel wäre die Formel nur für "Kosten".

Code:

If Worksheets("Tabelle1").Cells(i, 3).Value = "Kosten" Then

Jetzt habe ich aber nicht nur eine Formel für "Kosten", sondern auch für ca. 8 andere Status, deren Position in Spalte 3 oft variiert. Ich kann also zum Beispiel nicht sagen, "Zeile 10 in Spalte 3 benötigt sicher die Kosten Formel". 

Wenn ich alle Status in einer Formel zusammenfasse, wird die Excel Datei leider sehr langsam, da die Formel am Ende nicht nur für Spalte 24 verwendet wird, sondern nach dem einfügen bis Spalte 100 erweitert wird (1000 Zeilen x 76 Spalten = 76000 Zellen mit Formeln). Die Excel wird dadurch sehr groß, weshalb ich in der jeweiligen Zeile nur die Formel haben möchte die dort tatsächlich benötigt wird.

Deshalb mache ich das hier mal probeweise mit der "Kosten" Formel, am Ende würde ich das für jeden Status in VBA erstellen und hätte so weniger "Datenmüll".

[Klaus-Dieter]

Hallo,

am besten stellst du mal eine anonymisierte Beispieldatei mit allen Formeln ein, dann kann man konkrete Vorschläge machen. Möglicherweise gibt es andere Wege, als die von dir vorgesehenen.

[diving_excel]

Alles klar, im Anhang befindet sich eine stark vereinfachte Version der Datei. Habe das dort auf drei Status beschränkt und mit klick auf einen der Buttons wird die Formel entsprechend eingefügt.

Bin auch offen für andere Vorschläge, allerdings erschien mir das sehr nahe liegend.

Edit: Habe den Code auf das Beispiel angepasst

Danke & Gruß

[snb]

Du brauchst gar keine Excele Formel wenn du VBA verwendest.

Die Daten in Tabelle 2 und Tabelle3 fehlen.

[diving_excel]

Leider fehlen mir die VBA Kentnisse um das ganze ohne Formeln umzusetzen.

Habe das nun folgendermaßen gelöst:

Code:

Dim i As Long
For i = 33 To 2000


With Tabelle1

'Kosten
If .Cells(i, 3).Value = "Costs" Then
   .Cells(i, 24).Formula = "=IF(X$30<=TODAY(),$G$19*SUMIFS('Tabelle1'!$AC:$AC,'Tabelle1'!$AD:$AD,""0"",'Tabelle1'!$Y:$Y,LEFT('Tabelle1'!$F" & i & ",7)&""_""&'Tabelle1'!X$32),$G$19*SUMIFS('00 Import'!S:S,'Tabelle2'!$E:$E,LEFT('Tabelle1'!$F" & i & ",7)&""_Costs"",'Tabelle1'!$J:$J,""Latest""))"
End If

'Stunden
If .Cells(i, 3).Value = "Hours" Then
   .Cells(i, 24).Formula = "=IF(X$30<=TODAY(),SUMIF('Tabelle1'!$Y:$Y,LEFT('Tabelle1'!$F" & i & ",7)&""_""&'Tabelle1'!X$32,'Tabelle1'!$AD:$AD),SUMIFS('Tabelle2'!AD:AD,'Tabelle2'!$E:$E,LEFT('Tabelle1'!$F" & i & ",7)&""_Hours"",'Tabelle2'!$J:$J,""Latest""))"
End If

End With
Next i

 

Damit funktioniert das ganze wie gewünscht. Ließe sich dieser Code eventuell optimieren (Werde "long" schon mal durch "integer" ersetzen)? 

Lässt sich die letzte Zeile des Codes flexibel ermitteln? Also anstatt immer bis Zeile 2000 zu gehen immer nur bis zur letzten verwendeten Zelle in Spalte B?

Danke und Gruß

[schauan]

Hallöchen,

Long durch Integer zu ersetzen optimiert nicht besonders, wenn Du nicht gerade auf jedes bit Speicher angewiesen bist

Die letzte belegte Zeile ermittelst Du anhand Spalte B mit

lastRow=Cells(Rows.Count, 2).End(xlUp).Row


Optimieren könntest Du, wenn Duu z.B. alle Zellen mit cost filterst und in die sichtbaren Zellen mit einem Rutsch die Formel einträgst.
Das könnte dann so aussehen:

Sub Makro1()
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=1, Criteria1:="Ton"
Range("B2:B7").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[-1]"
End Sub

mit dem Ergebnis

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	Wer	Was
2	An
3	Ton	Ton
4	Ber
5	Ta
6	An
7	Ton	Ton

Zelle	Formel
B3	=A3
B7	=A7

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

Den Code dazu könntest Du ggf. aufzeichnen. Für die Formel sollte die Eingabe in eine Zelle reichen. Statt der 7 in meinem COde kommt dann die Sache mit dem lastRow.