VBA Formel anpassen

Forumthread: VBA Formel anpassen

VBA Formel anpassen

26.10.2020 08:50:13

Clara

Guten Morgen zusammen,
in einem Makro von mir möchte ich gern folgende Formel anpassen:

Sub Formel()
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS([AK / BW (Gesamt)],[Datum],"">=""&R3C2,[Datum] _
,""=""&R3C2,[Datum],""

Die beiden fettgedruckten Teile (R3C2) dürfen nicht wie bisher starr auf $B$3 verweisen, sondern sollen auf folgende Zelle verweisen:

Sub NeueZelle()
Range("B5000").End(xlUp).Offset(-2, 0).Select
End Sub

Diese muss dann aber letztendlich in der finalen Formel auch ein $[...]$[...] aufweisen.
Wie gehe ich hier vor?
VG und einen schönen Start in die Woche
Clara

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA Formel anpassen

26.10.2020 09:33:12

Daniel

Hi
Ersetze die Zeilennummer 3 im R3C2
Durch " & Cells(5000, 2).End(xlup).Offset(-2 ,0).Row & "
Die Anführungszeichen sind hier teil des einzufügenden Textes.
Gruß Daniel

AW: VBA Formel anpassen (danke!)

26.10.2020 09:46:01

Clara

Danke Daniel!
Ich habe genau das gesucht, wie man einen Teil der Formel anpassen kann. Das mit dem "& ...&" wusste ich noch nicht.
Der Makrorecorder nimmt sowas ja als R1C1 auf. Falls ich die Formel via Formula (also Englisch) aufschreiben würde, könnte ich das darin enthalten &$B$3 ebenso über deine Funktionsergänzung abändern?
VG

AW: VBA Formel anpassen (danke!)

26.10.2020 09:51:44

Daniel

Hi
Ja kannst du machen.
Aber wozu?
R1C1 ist in der Regel für VBA die praktischere Addressierungsart, weil sie Relative Zellbezüge auch relativ beschreiben kann und Spalten ebenfalls als Zahl angegeben werden, was eine Berechnung von Spaltenbezügen vereinfacht.
Gruß Daniel

AW: VBA Formel anpassen (danke!)

26.10.2020 09:59:11

Clara

Danke für die Erklärung.
Ich bekomme beim Einbau in mein Makro nun leider eine Fehlermeldung.

Sub NeueAnlage()
Application.ScreenUpdating = False
Tabelle2.Select
Range("A1").Select
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Copy Tabelle1.Range("A5000").End( _
xlUp).Offset(2, 0)
Tabelle1.Select
Range("I5000").End(xlUp).Offset(-3, 0).Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS([AK / BW (Gesamt)],[Datum],"">=""&R" & Cells(5000, _
2).End(xlUp).Offset(-2, 0).Row & "C2,[Datum],""=""&R" & Cells(5000, 2).End(xlUp).Offset(-2, 0).Row & "C2,[Datum],""

Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler
Debuggen verweist auf die FormulaR1C1
Gruß
Clara

AW: VBA Formel anpassen (danke!)

26.10.2020 10:17:00

Daniel

Hi
Lass dir die Formel erstmal als Textstring anzeigen (MsgBox "=Iferror..." oder debug.Print "=Iferror(...")
Und kontrollieren, ob du dir da eine Korrekte Formel zusammen gebastelt hast.
Falls nein, siest du ja die Stelle, an der du deinen Code korrigieren musst.
Sollte der alte Code mit dem festen Zellbezug R3C2 funktioniert haben, dann gibt es auch einen eleganteren Weg, um solche Variablen in die Formel einzubauen:
1. Schreibe die Formel in eine Stringvariable.
Ersetzte dabei den Zahlenwert, der durch eine berechneten Wert gebildet werden soll, durch einen DummyText, der so in der Formel nicht vorkommt.
2. ersetze mit Replace den DummyText durch den Berechneten Wert.
Das ist zwar etwas mehr Code, aber deutlich übersichtlicher und weniger Fehleranfällig als die Formel aus Teiltexten zusammenzusetzen:

FO = "=IfError(...R_x_C2...R_x_C2...)"
FO = Replace(FO, "_x_", Cells(5000, 2).End(xlUp).Offset(-2, 0).Row)
... .FormulaR1C1 = FO

Gruß Daniel

AW: VBA Formel anpassen (danke!)

26.10.2020 10:44:23

Clara

Das ist eine gute Idee. Macht die Sache zwar länger aber sauberer.
Habe mittlerweile meinen Fehler gefunden. Somit funktioniert die "alte" Version wieder.
Deine neue Idee habe ich auch mal umgesetzt, um zu lernen, jedoch funktioniert diese im Gegensatz zur "alten" Version nicht. Erkenne aber den Unterschied nicht, da es eigentlich zum identischen Ergebnis kommen müsste.

Sub NeueAnlage()
Application.ScreenUpdating = False
Tabelle2.Select
Range("A1").Select
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Copy Tabelle1.Range("A5000").End( _
xlUp).Offset(2, 0)
Tabelle1.Select
Range("I5000").End(xlUp).Offset(-3, 0).Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS([AK / BW (/Gesamt)],[Datum],"">=""&R" & Cells( _
5000, 2).End(xlUp).Offset(-2, 0).Row & "C2,[Datum],""=""&R" & Cells(5000, 2).End(xlUp).Offset(-2, 0).Row & "C2,[Datum],""

Sub NeueAnlage2()
Dim FO As String
FO = "=IFERROR(SUMIFS([AK / BW (/Gesamt)],[Datum],"">=""&R_x_C2,[Datum],""=""&R_x_C2,[Datum],""

In der zweiten Version verweist der Replace-Einschub auf eine Row, die 7 Rows zu weit oben ist.

AW: VBA Formel anpassen (danke!)

26.10.2020 10:52:19

Daniel

Dann berechnet du das falsch.
Da ich deine Datei und Aufgabenstellung nicht kenne, kann ich dir auch nicht sagen, was da richtig und falsch ist.
Gruß Daniel

Klappt alles. Besten Dank Daniel owT

26.10.2020 10:58:29

Clara

Beliebteste Forumthreads (12 Monate)

Forumthread: VBA Formel anpassen

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

VBA Formel anpassen

Forumthread: VBA Formel anpassen

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread