Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

VBA Formel anpassen

Betrifft: VBA Formel anpassen von: Clara
Geschrieben am: 26.10.2020 08:50:13

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] _
,""<=""&RC[-7])/SUMIFS([Menge],[Datum],"">=""&R3C2,[Datum],""<=""&RC[-7]),"""")"
End Sub
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

Betrifft: AW: VBA Formel anpassen
von: Daniel
Geschrieben am: 26.10.2020 09:33:12

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

Betrifft: AW: VBA Formel anpassen (danke!)
von: Clara
Geschrieben am: 26.10.2020 09:46:01

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

Betrifft: AW: VBA Formel anpassen (danke!)
von: Daniel
Geschrieben am: 26.10.2020 09:51:44

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

Betrifft: AW: VBA Formel anpassen (danke!)
von: Clara
Geschrieben am: 26.10.2020 09:59:11

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],""<=""&RC[-7])/SUMIFS([Menge],[Datum],"">=""&R" & Cells(5000, 2).End(xlUp).Offset(-2, 0).Row & "C2,[Datum],""<=""&RC[-7]),"""")"
    
    Range("A5000").End(xlUp).End(xlUp).Offset(0, 2).Select
    
    Application.ScreenUpdating = True
    
End Sub
Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler

Debuggen verweist auf die FormulaR1C1

Gruß
Clara

Betrifft: AW: VBA Formel anpassen (danke!)
von: Daniel
Geschrieben am: 26.10.2020 10:17:00

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

Betrifft: AW: VBA Formel anpassen (danke!)
von: Clara
Geschrieben am: 26.10.2020 10:44:23

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],""<=""&RC[-7])/SUMIFS([Menge],[Datum],"">=""&R" & Cells(5000, 2).End(xlUp).Offset(-2, 0).Row & "C2,[Datum],""<=""&RC[-7]),"""")"
    
    Range("A5000").End(xlUp).End(xlUp).Offset(0, 2).Select
    
    Application.ScreenUpdating = True
    
End Sub

Sub NeueAnlage2()

    Dim FO As String
    FO = "=IFERROR(SUMIFS([AK / BW (€/Gesamt)],[Datum],"">=""&R_x_C2,[Datum],""<=""&RC[-7])/ _
SUMIFS([Menge],[Datum],"">=""&R_x_C2,[Datum],""<=""&RC[-7]),"""")"
    FO = Replace(FO, "_x_", Cells(5000, 2).End(xlUp).Offset(-2, 0).Row)
    
    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 = FO
    
    Range("A5000").End(xlUp).End(xlUp).Offset(0, 2).Select
    
    Application.ScreenUpdating = True
    
End Sub
In der zweiten Version verweist der Replace-Einschub auf eine Row, die 7 Rows zu weit oben ist.

Betrifft: AW: VBA Formel anpassen (danke!)
von: Daniel
Geschrieben am: 26.10.2020 10:52:19

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

Betrifft: Klappt alles. Besten Dank Daniel owT
von: Clara
Geschrieben am: 26.10.2020 10:58:29



Beiträge aus dem Excel-Forum zum Thema "VBA Formel anpassen"