Excel VBA Formula mit Variabeln

Forumthread: Excel VBA Formula mit Variabeln

06.07.2017 09:42:05

Nik

Hallo zusammmen,
habe ein kleines Problem: Ich möchte folgende VBA Formel unter eine Tabelle setzen.

=SUMMEWENNS(P2:P151;$A$2:$A$151;"GME ASM")

Da die Tabelle nicht immer gleich lang ist lese ich per Befehl "SET" vorher die festen und relativen Zellbezüge der Tabelle aus.
Das Problem bereitet mir der jedoch Suchbegriff "GME ASM" in der VBA Formel, da dieser in Anführungszeichen steht. Irgendwie bekomme ich das nicht hin.
Auch wenn ich es per Variabel vorher festlege als X = """GME AS""" oder es mit Char(34) vorher und nach dem "GME ASM" definiere.
Habe schon etliche Varianten probiert. Vielleicht kann mir jemand die Richtung weisen oder mich auf meinen Fehler hinweisen.
Bin für alle Hinweise dankbar!
https://www.herber.de/bbs/user/114717.xlsb
Hie der Code:

Sub TEST()
Dim c As Range
Dim a As Range
Dim e As Range
Dim aa As Range
Dim ee As Range
Dim x As String
letzteZeile = Selection.CurrentRegion.Rows.Count
Set a = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).End(xlUp).Offset(1, 1)
Set e = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).Offset(0, 1)
Set aa = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).End(xlUp).Offset(1, 0)
Set ee = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
Set c = ActiveSheet.Cells(Rows.Count, 15).End(xlUp)
x = Chr(34) & "GME ASM" & Chr(34)
With c
.Offset(3, 0).Value = "Total GME"
.Offset(3, 1).NumberFormat = "0_ ;[Red]-0 "
.Offset(3, 1).Formula = "=SUMMEWENNS(" & a.Address(0, 0) & ":" & e.Address(0, 0)  _
& ";" & aa.Address(1, 1) & ":" & ee.Address(1, 1) & ";" & x & ")"
End With
End Sub

Gruß
Nik

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: mit FormulaLocal

06.07.2017 10:04:21

hary
AW: mit FormulaLocal

06.07.2017 10:39:18

Nik
Schaue es Dir mal...

06.07.2017 10:04:48

Case
AW: Excel VBA Formula mit Variabeln

06.07.2017 10:57:55

fcs
AW: Excel VBA Formula mit Variabeln

10.07.2017 08:38:23

Nik

AW: mit FormulaLocal

06.07.2017 10:04:21

hary

Moin
Formula erwartet englische Schreibweise.
Du musst FormulaLocal nehmen, deutsche schreibweise

.Offset(3, 1).FormulaLocal

gruss hary

AW: mit FormulaLocal

06.07.2017 10:39:18

Nik

Oh mann !! Da muss man aber auch auf jede Kleinigkeit achten.
Naja, wieder etwas dazugelernt. :-)
Danke an euch beide !!!
Funzt jetzt Prima! :-D

Schaue es Dir mal...

06.07.2017 10:04:48

Case

Hallo Nik, :-)
... im Beispiel an: ;-)
Beispieldatei...
Servus
Case

AW: Excel VBA Formula mit Variabeln

06.07.2017 10:57:55

fcs

Hallo Nik,
das mit dem FormulaLocal hast du ja schon.
Es ist aber meist einfacher - wenn man sich mal daran gewohnt hat ;-) - unter VBA die Formeln in der R1C1-Schreibweise zu generieren. Zusätzlich sollte man die US-Funktionsnamen verwenden.
Im Zweifel hilft hier der Makro-Recorder.
Das hat den Vorteil - dass das Ganze unabhängig von System-Spracheinstellung wird.
Gruß
Franz

Sub TEST_US_R1C1()
Dim ZL As Long, Z1 As Long
Z1 = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).End(xlUp).Row
ZL = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).Row
With ActiveSheet.Cells(ZL, 15)
.Offset(3, 0).Value = "Total GME"
.Offset(3, 1).NumberFormat = "0_ ;[Red]-0 "
.Offset(3, 1).FormulaR1C1 = _
"=SUMIFS(R" & Z1 & "C16:R" & ZL & "C16, R" & Z1 & "C1:R" & ZL & "C1,""GME ASM"")"
End With
End Sub
Sub TEST_US_R1C1_2()
Dim ZL As Long, Z1 As Long
Z1 = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).End(xlUp).Row
ZL = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).Row
With ActiveSheet.Cells(ZL + 3, 15)
.Value = "Total GME"
.Offset(0, 1).NumberFormat = "0_ ;[Red]-0 "
.Offset(0, 1).FormulaR1C1 = _
"=SUMIFS(R" & Z1 & "C:R[-3]C, R" & Z1 & "C1:R[-3]C1,""GME ASM"")"
End With
End Sub

AW: Excel VBA Formula mit Variabeln

10.07.2017 08:38:23

Nik

Hallo Franz,
das hört sich nicht nur plausibel an, sondern sieht auch aufgeräumter aus.
Werde das in Zukunft genau so halten.
Danke und Gruß! :-)
Nik

Infobox / Tutorial

Excel VBA Formel mit Variablen effizient nutzen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Neues Modul erstellen: Rechtsklicke im Projektfenster auf Einfügen > Modul.

Code eingeben: Füge den folgenden Code in das Modul ein:

Sub TEST()
   Dim c As Range
   Dim a As Range
   Dim e As Range
   Dim aa As Range
   Dim ee As Range
   Dim x As String
   letzteZeile = Selection.CurrentRegion.Rows.Count
   Set a = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).End(xlUp).Offset(1, 1)
   Set e = ActiveSheet.Cells(Rows.Count, 15).End(xlUp).Offset(0, 1)
   Set aa = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).End(xlUp).Offset(1, 0)
   Set ee = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
   Set c = ActiveSheet.Cells(Rows.Count, 15).End(xlUp)
   x = Chr(34) & "GME ASM" & Chr(34)
   With c
       .Offset(3, 0).Value = "Total GME"
       .Offset(3, 1).NumberFormat = "0_ ;[Red]-0 "
       .Offset(3, 1).Formula = "=SUMMEWENNS(" & a.Address(0, 0) & ":" & e.Address(0, 0) & ";" & aa.Address(1, 1) & ":" & ee.Address(1, 1) & ";" & x & ")"
   End With
End Sub

Code ausführen: Drücke F5, um den Code auszuführen und die Formel in die Zelle einzufügen.

Häufige Fehler und Lösungen

Problem mit Anführungszeichen: Wenn Du den Suchbegriff in Anführungszeichen einfügen möchtest, nutze Chr(34) für die korrekte Darstellung. Beispiel: x = Chr(34) & "GME ASM" & Chr(34).
Falsche Formel-Syntax: Achte darauf, die deutsche Schreibweise für Formeln zu verwenden, indem Du .FormulaLocal anstelle von .Formula nutzt.
```
.Offset(3, 1).FormulaLocal = "=SUMMEWENNS(...)"
```

Alternative Methoden

Eine praktische Alternative zur Nutzung der Standard-Formelsyntax ist die Verwendung von R1C1-Schreibweise in VBA. Damit bleibt der Code unabhängig von der Spracheinstellung:

.Offset(3, 1).FormulaR1C1 = "=SUMIFS(R[0]C16:R[5]C16, R[0]C1:R[5]C1,""GME ASM"")"

Diese Methode ist besonders nützlich, wenn Du mit dynamischen Bereichen arbeitest.

Praktische Beispiele

Hier sind einige Beispiele, wie Du Formeln in VBA effizient einsetzen kannst:

Einfache Summenberechnung:
```
.Offset(3, 1).Formula = "=SUM(A1:A10)"
```

Verwendung von Variablen in der Formel:

Dim suchbegriff As String
suchbegriff = "GME ASM"
.Offset(3, 1).Formula = "=SUMMEWENNS(B1:B10, A1:A10, """ & suchbegriff & """)"

Tipps für Profis

Makro-Recorder nutzen: Der Makro-Recorder kann Dir helfen, den richtigen VBA-Code für komplexe Formeln zu generieren. Achte darauf, den aufgezeichneten Code zu optimieren.
VBA-Fehlerbehandlung: Implementiere On Error Resume Next, um potenzielle Fehler während der Codeausführung zu ignorieren und die Fehlersuche zu vereinfachen.

FAQ: Häufige Fragen

1. Wie kann ich eine Formel in eine Zelle einfügen? Du kannst die Formel direkt in die Zelle einfügen, indem Du die .Formula oder .FormulaLocal Methode verwendest.

2. Was ist der Unterschied zwischen .Formula und .FormulaLocal? .Formula verwendet die englische Syntax, während .FormulaLocal die lokale Spracheinstellung verwendet, die für die Formel notwendig ist.

3. Wie kann ich eine dynamische Zellreferenz in einer Formel verwenden? Verwende die Address-Methode, um die Zellreferenzen dynamisch zu generieren, z.B. a.Address(0, 0).

4. Was ist die R1C1-Schreibweise? Die R1C1-Schreibweise ermöglicht es, auf Zellen relativ zu ihrer Position zuzugreifen, was in bestimmten Szenarien nützlich ist, um die Lesbarkeit zu erhöhen.

Beliebteste Forumthreads (12 Monate)