Office: (Office 2007) Formel in VBA dynamisch machen

Hi Leute,

ich arbeite seit längerem mit einer Excel-Tabelle, die mit Hilfe eines selbst erstellten Makros aufbereitet werden soll.

So zum Beispiel soll in einer Zeile (ABER nicht in allen Zellen) die Formel =Teilergebnis(...) eingesetzt werden.

Für eine Zelle sähe der VBA-Code so aus:

Cells(Z_unten + 1, 7).Select
Selection.Formula = "=Teilergebnis(9,G2:G82)"
Selection.FormulaR1C1 = "=SUBTOTAL(9,R[-301]C:R[-1]C)"

Da ich das alles in eine Schleife mit einer inneren Bedingung setzen möchte, habe ich die Frage, wie ich die Bereichsangabe innerhalb der Formel "=Teilergebnis" dynamisch kriege. Ich muss dabei beachten, dass die Tabelle immer unterschiedlich lang ist (oben im Beispiel mit 82 Zeilen angegeben...es können aber auch weniger oder mehr sehr sein).

Ich hoffe, ich hab' das so verständlich ausgedrückt.

Ich bin für jeden Tipp dankbar.

Grüsse

Steffie

:)

 

Hallo Steffie,

in VBA kann zu 99% auf select verzichtet werden.

Cells(Z_unten + 1, 7).Formula = "=Teilergebnis(9,G2:G" & Z_unten & ")"

 

danke, dass hilft schon...aber wie sieht das mit den Spalten aus.

im Bsp. habe ich Spalte "G" gewählt. beim Objekt Cells brauch ich da ja nur auch einen Zähler einbauen...aber wie sieht dann die Formel "=Teilergebnisse" aus? Diese muss sich doch dann auch anpassen, ansonsten habe ich die selbe Formel in verschiedenen Zellen nebeneinander, die sich alle auf den selben Bereich beziehen?

Gruss

Steffie

 

Hallo Steffie

Code:

 

danke...ich werd's mal ausprobieren und dann 'ne kurze Rückmeldung geben!!!

LG

Steffie

 

so...hab's jetzt mal ausprobiert. Leider klappt es immer noch nicht. Es treten zwei Probleme auf. Zuerst muss ich aber sagen, dass er wenigstens die Formel überall einträgt(auch mit dem richtigen Bereich):

Nun zu den Problemen:
1) In der Zelle steht zwar jetzt die richtige Formel, aber das Ergebnis ist "0"...warum?

2)der Kompiler erkennt meine eingebaute Abbruchbedingung nicht.

Hier ist mein Code (mal zum Drüberschaun):

Public Sub Teilergebnis()

Dim InSpalte As Integer
Dim Z_unten As Long
Dim letztZeile As Long 'hier wird letzte Zeile gespeichert
Dim spalte As Integer

'Schritt 1: Suchen der letzten Zeile, in der die Summen aufgeführt sind.
i = 1
letztZeile = 1
Do
If Sheets("Sheet1").Cells(i, 1).Value "Gesamtergebnis" Then
letztZeile = letztZeile + 1
End If
i = i + 1
Loop Until Cells(i, 1).Value = "Gesamtergebnis" 'letzte Zeile der Tabelle gefunden

'Schritt 2: Formel dynamisch einsetzen
i = 1
Z_unten = letztZeile
InSpalte = 7
Do
If Sheets("Sheet1").Cells(1, i).Value "I VSP %" Then
Cells(Z_unten, InSpalte).Formula = "=SUBTOTAL(9," & Cells(2, InSpalte).Address(False, False) & ":" & Cells(Z_unten, InSpalte).Address(False, False) & ")"
End If
InSpalte = InSpalte + 1
i = i + 1
Loop Until Cells(1, i).Value = "I VSP %"

End Sub

Könnt Ihr da einen Fehler entdecken? Die Abbruchbedingung, die nicht erkannt wird ist in den folgenden Ausdrücken:
a) Sheets("Sheet1").Cells(1, i).Value "I VSP %"
b) Loop Until Cells(1, i).Value = "I VSP %"

Gruss
Steffie

 

Hi Steffie,

Durch die Formel produzierst Du einen Zirkelbezug.

Abhilfe schaffst Du, indem Du in der Zeile noch -1 einträgst - hab ich Fett markiert!

Cells(Z_unten, InSpalte).Formula = "=SUBTOTAL(9," & Cells(2, InSpalte).Address(False, False) & ":" & Cells(Z_unten -1, InSpalte).Address(False, False) & ")"

Wobei ich hier den Anwendungsfehler noch nicht gefunden hab. Der Zirkelbezug ist hierdurch jedoch weg.

Edit:

Deine Abbruchbedingung funzt, wenn Du es wie folgt schreibst:

'Schritt 2: Formel dynamisch einsetzen
i = 1
Z_unten = letztZeile
For InSpalte = 7 To 255
If Sheets("Tabelle1").Cells(1, i).Value = "I VSP %" Then
Exit Sub
End If
If Sheets("Tabelle1").Cells(1, i).Value "I VSP %" Then
Cells(Z_unten, InSpalte).Formula = "=SUBTOTAL(9," & Cells(2, InSpalte).Address(False, False) & ":" & Cells(Z_unten - 1, InSpalte).Address(False, False) & ")"
End If
i = i + 1
Next InSpalte
End Sub

greetz

Schmali

 

danke Schmali...das hat geholfen...wenigstens für mein eines Problem...

...jetzt ist also nur noch die Frage offen, warum meine Abbruchbedingung nicht erkannt wird (übrigens Schreibweise gleicht dem Zelleninhalt -> daran kann's also net liegen)

gruß

Steffie

 

Hi Steffie,

ich hab grad meinen letzten Post noch mal editiert und die Abbruchbedingung funzt nicht, da die Variable "InSpalte" den Wert 257 annimmt und Excel nur 256 Spalten (vielleicht auch 255) hat.

etwas umgestellt müsste es jetzt eigentlich gehen.

greetz

schmali

 

hi Schmali,

wie meinst du denn "etwas umgestellt"?

lg

steffie

 

ich hab die abbruchbedingung umgeschrieben, da sie bei mir einen Anwendungsfehler erzeugt hat.

Hier nochmal die Abbruchbedingung:

--------------------

'Schritt 2: Formel dynamisch einsetzen
i = 1
Z_unten = letztZeile
For InSpalte = 7 To 255
If Sheets("Tabelle1").Cells(1, i).Value = "I VSP %" Then
Exit Sub
End If
If Sheets("Tabelle1").Cells(1, i).Value "I VSP %" Then
Cells(Z_unten, InSpalte).Formula = "=SUBTOTAL(9," & Cells(2, InSpalte).Address(False, False) & ":" & Cells(Z_unten - 1, InSpalte).Address(False, False) & ")"
End If
i = i + 1
Next InSpalte
End Sub

------------------

Zwar kommt keine Fehlermeldung, jedoch bricht die Schleife auch nicht ab, wo sie soll - zumindestens nicht bei mir *wink.gif*

greetz

 

Hi Steffie,

so eigentlich antwortet man nicht auf den eigenen Beitrag, aber ich tu das trotzdem mal.

Ich hab noch ein bisschen was geändert und das funzt jetzt auch - zumindestens mit meiner Versuchsdatei.

Ich pack die hier mal als Anlage ran. Kannst ja mal probieren, ob das an das rankommt, was Du brauchst.

Greetz

Schmali

 

Hallo Schamli,

nicht registrierte User können nichts runterladen.

 

Hi Hajo,

tja, dann sollte sie sich anmelden - ist doch eh das Beste, registriert zu sein. *wink.gif*

Schmali