Microsoft Excel

Herbers Excel/VBA-Archiv

Dynamisch einen Fomelstring aufbauen- VBA


Betrifft: Dynamisch einen Fomelstring aufbauen- VBA von: Nilo
Geschrieben am: 03.04.2018 15:52:24

Hi zusammen,
ich hoffe ihr könnt mir kurz helfen!

Ich habe eine Tabelle
In der Tabelle habe ich per Formeln Bezüge für den Index hergestellt.

Beispiel für: F$6&$C$6
F6:

  • [dieseDaten.xlsx]Daten

  • ist immer Fix auf F6!

    C6:
  • =SVERWEIS(B3;Spalten!A:C;3;0)

  • in C6 steht als Bezug z.B. !AB:AB

    Am Ende habe ich mehrere Bezüge per Index in eine Formel gepackt.
    Das klappt sehr gut da ich somit dynamisch meine Berechnungen quasi zusammenstecken kann.

    Blöd ist jetzt das die Formel in ihren Bezügen nicht dynamisch ist!

    Wenn in der ug Formel zb INDIREKT(F$6&$C$8);$D$8 C8 oder D8 leer ist haut das nicht hin.

    Ich dachte jetzt mit VBA könnte ich die einzelnen Blöcke quasi als Stringblöcke vordefinieren und abfragen ob der Bezug leer ist oder nicht und danch den Formelstring dynamisch aufbauen.

    Es geht um die Fettgedruckten Bezüge:
  • =WENNFEHLER(SUMMEWENNS(INDIREKT(F$6&$C$3);INDIREKT(F$6&$C$6);$D$6;INDIREKT(F$6&$C$7);$D$7;INDIREKT(F$6&$C$8);$D$8;INDIREKT(F$6&$C$9);$D$9;INDIREKT(F$6&$C$10);$B11);"n.a")


  • Als Beispiel:
    C8 = leer
    D8 = leer
    Dann würde dieser Block wegfallen: INDIREKT(F$6&$C$8);$D$8

    Dann so:
  • =WENNFEHLER(SUMMEWENNS(INDIREKT(F$6&$C$3);INDIREKT(F$6&$C$6);$D$6;INDIREKT(F$6&$C$7);$D$7;INDIREKT(F$6&$C$9);$D$9;INDIREKT(F$6&$C$10);$B11);"n.a")


  • Wie könnte der Formelstring in VBA aufgebaut werden?

    Die Anzahl der möglichen Bezüge wäre Fix auf 6 eingestellt
    Also:
    INDIREKT(F$6&$C$6)$D$6
    INDIREKT(F$6&$C$7);$D$7
    INDIREKT(F$6&$C$8);$D$8
    INDIREKT(F$6&$C$9);$D$9
    INDIREKT(F$6&$C$10);$D$10
    INDIREKT(F$6&$C$11);$D$11

    Ich hoffe das ist verständlich erklärt.

    Danke für eine Rückmeldung und Gruß
    Nilo


    Ich habe da keinen Plan

      

    Betrifft: AW: Dynamisch einen Fomelstring aufbauen- VBA von: ChrisL
    Geschrieben am: 03.04.2018 16:26:56

    Hi Nilo

    Wie eine solche Datenbasis entstehen kann, entzieht sich meiner Vorstellungskraft.

    Vielleicht so...

    Sub t()
    Dim i As Integer, s As String
    
    If Range("F6") = "" Or WorksheetFunction.CountBlank(Range("C6:C11")) = 6 Or _
    WorksheetFunction.CountBlank(Range("D6:D11")) = 6 Then Exit Sub
    
    For i = 6 To 11
        If Not Cells(i, 3) = "" And Not Cells(i, 4) = "" Then
            s = s & ",INDIRECT(F$6&$C$" & i & "),$D$" & i
        End If
    Next i
    
    Range("A1").Formula = "=IFERROR(SUMIFS(INDIRECT(F$6&$C$3)" & s & "),""n.a."")"
    End Sub
    cu
    Chris


      

    Betrifft: AW: Dynamisch einen Fomelstring aufbauen- VBA von: onur
    Geschrieben am: 03.04.2018 19:36:44

    Warum willst du vba benutzen, um Formeln in zellen zu schreiben, statt alles direkt mit vba zu lösen?


      

    Betrifft: Kurzes Feedback... von: Nilo
    Geschrieben am: 03.04.2018 22:07:41

    Hi zurück,

    erstmal @ Chris: vielen Dank, so was hab ich versucht und selbst nicht hinbekommen.
    Läuft perfekt :)

    @ Onur: eine einfache VBA Anweisung habe ich mir schon diverse aus dem Netz zusammengesucht,
    aber beim erweitern und dynamisch halten steig ich dann wieder aus. Da bin ich eher der Formeltyp
    da ich die wiederum besser interpretieren kann.
    Sowas wie oben geht natürlich auch nur mit Formeln und Hilfsspalten etc. aber mit dem Konstrukt muss ich die Formelebene nicht verlassen.

    Mal doof gefrag: wie würde den das VBA zu der og Formel aussehen? Ich habe da keinen Plan!

    VG
    Nilo


      

    Betrifft: AW: Kurzes Feedback... von: onur
    Geschrieben am: 03.04.2018 23:06:04

    Keine Ahnung, ich analysiere nicht irgendwelche Formeln von Anderen.
    Solltest schon sagen, was genau du erreichen willst.


      

    Betrifft: AW: Kurzes Feedback... von: Nilo
    Geschrieben am: 04.04.2018 13:03:44

    Hi Onur,

    Prinzipiell so wie die genannte Summewenns Formel!

    Ohne Indirekt dann so:

    =WENNFEHLER(SUMMEWENNS([Daten.xlsx]Tabelle2!$AL:$AL;[Daten.xlsx]Tabelle2!$H:$H;$D$6; [Daten.xlsx]Tabelle2!$AB:$AB;$D$7;[Daten.xlsx]Tabelle2!$AS:$AS;$D$8;[Daten.xlsx]Tabelle2!$AQ:$AQ; $D$9;[Daten.xlsx]Tabelle2!$AM:$AM;$D$10;[Daten.xlsx]Tabelle2!$L:$L;$B$12);"n.a.")

    Die Kriterien stehen dann in den Zellen im aktuellen Blatt oder wo auch immer!

    Wie würde für diese Formel jetzt eine Fkt oder VBA aussehen wenn zugleich dynmisch bleiben soll
    dass wenn ich [Daten.xlsx]Tabelle2!$AM:$AM;$D$10; nicht als Krtierium angeben will es auch möglich ist?

    Wenn Du da eine Idee hast!?

    Danke und Gruß
    Nilo


      

    Betrifft: AW: Kurzes Feedback... von: onur
    Geschrieben am: 04.04.2018 13:34:55

    Wie gesagt, "ich analysiere nicht irgendwelche Formeln von Anderen".
    WAS soll die Formel GENAU machen (Am Besten mal mit Beispieldatei)?


    Beiträge aus dem Excel-Forum zum Thema "Dynamisch einen Fomelstring aufbauen- VBA"