Formel aus Zelle in VBA einlesen und ändern

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 10:56:14

Hallo,
wie kann ich per VBA eine Formel einlesen und Teile der Formel ändern? Die Formel wird nicht in die Zelle zurückgeschrieben, sondern in VBA weiter verarbeitet.
=VERKETTEN(InputB!A2;"||";InputA!F2;"||";InputA!B2;"||";InputB!G2;"||"
Hier muss ich nun die "2" bei den InputA-Zellangaben in eine 3 ändern. Mit "Replace" geht es nicht, da es unterschiedliche Spalten betrifft
Habt ihr da evtl eine Idee? Vielen Dank im Voraus!
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Michael (migre)
Geschrieben am: 11.09.2015 11:20:07
Hallo Herbert!
Aber so funktioniert es schon mit Replace... oder hab ich Dich falsch verstanden:

Sub a()
    Dim Formel As String
    
    Formel = ActiveCell.Formula
    
    Debug.Print Formel
    
    Formel = Replace(Formel, "2,", "3,", 1)
    
    Debug.Print Formel
    
End Sub
LG
Michael

Bild

Betrifft: AW: Ergänzung...
von: Michael (migre)
Geschrieben am: 11.09.2015 11:22:05
Hallo Herbert,
... mein voriger Ansatz ergibt bei mir:
vor Replace
=CONCATENATE(InputB!A2,"||",InputA!F2,"||",InputA!B2,"||",InputB!G2,"||")
nach Replace
=CONCATENATE(InputB!A3,"||",InputA!F3,"||",InputA!B3,"||",InputB!G3,"||")
So hätte ich es verstanden...
LG
Michael

Bild

Betrifft: AW: Meine Beiträge = Blödsinn, mein Fehler...
von: Michael (migre)
Geschrieben am: 11.09.2015 11:24:49
Sorry, hab jetzt erst verstanden, dass es Dir um InputA geht und Du auch InputB dabei hast!
Tut mir leid - wer lesen kann ist klar im Vorteil!
LG
Michael

Bild

Betrifft: AW: Offen! owT
von: Michael (migre)
Geschrieben am: 11.09.2015 11:37:02


Bild

Betrifft: AW: Offen! owT
von: Herbert Grom
Geschrieben am: 11.09.2015 12:24:02
Hallo Michael,
vielen Dank für Deinen Versuch. Der gute Wille zählt! ;o)=)
Aber vielleicht hat ja noch jemand eine Idee!
Servus

Bild

Betrifft: AW: Die entscheidende Frage ...
von: Gerd L
Geschrieben am: 11.09.2015 12:26:52
Hallo Herbert!
... ist, ob der neue Text oder das neue Formelergebnis in eine Variable sollen.
Gruß Gerd

Bild

Betrifft: AW: Die entscheidende Frage ...
von: Herbert Grom
Geschrieben am: 11.09.2015 15:46:52
Hallo Gerd,
diese Frage ist allerdings berechtigt, denn es soll das neue Formelergebnis in eine Variable. Warum fragst Du?
Servus

Bild

Betrifft: AW: Die entscheidende Frage ...
von: Daniel
Geschrieben am: 11.09.2015 16:27:50
Hi
nun, dann müsstest du doch die neue Formel in die Zelle schreiben und den Wert der Zelle ins Array übertragen.
oder du berechnest mit =Evaluate(FormelText) das Formelergebnis der neuen Formel direkt in VBA.
Dann muss allerdings die Formel in Englisch geschrieben sein (.Formula statt .FormulaLocal)
Gruß Daniel

Bild

Betrifft: AW: Kleine Rehabilitation meinerseits + schönes WE
von: Michael (migre)
Geschrieben am: 11.09.2015 13:52:44
Hallo Herbert und @ alle!
Mich hat's ziemlich gewurmt, dass ich nicht gleich ordentlich gelesen hab, und daher die beiden unnötigen Antworten verzapft habe; und daher hab ich mich nochmal vor dem Wochenende besonnen und auch eine Lösung erbastelt.
Ist mittlerweile unnötig, und mit Kapazundern wie Daniel und Rudi kann ich mich leider nicht mal im Ansatz messen, aber ich poste hier noch meine Lösung, weil ich das für mein eigenes VBA-Gewissen brauche ;-). Soweit ich es jetzt mal getestet hab müssten auch 2- und 3-Buchstaben- umfassende Spaltenbezüge möglich sein.

Sub FormelTausch()
    Dim Formel As String
    Dim Zeichen As String
    Dim FolgeZeichen As String
    Dim i As Integer
    Dim Merker As Boolean
    Dim Sammler As String
    
    Formel = ActiveCell.FormulaLocal
    
    Debug.Print Formel
    
    For i = 1 To Len(Formel)
        Zeichen = Mid(Formel, i, 1)
        
        Select Case IsNumeric(Zeichen)
            Case Is = True
                If Merker = True Then
                    Sammler = Sammler & "3"
                    Merker = False
                Else: Sammler = Sammler & Zeichen
                End If
            Case Is = False
                FolgeZeichen = Mid(Formel, i + 1, 1)
                Select Case FolgeZeichen
                    Case Is = "!"
                        Select Case Zeichen
                            Case Is = "A"
                                Merker = True
                                Sammler = Sammler & Zeichen
                            Case Else
                                Sammler = Sammler & Zeichen
                        End Select
                    Case Else
                        Sammler = Sammler & Zeichen
                End Select
        End Select
    Next
    
    Debug.Print Sammler
    
End Sub
Danke auch an alle Antworter hier - da hab ich auch gleich wieder was gelernt ;-). Allen wünsche ich damit ein schönes Wochenende!
LG
Michael

Bild

Betrifft: AW: Kleine Rehabilitation meinerseits + schönes WE
von: Herbert Grom
Geschrieben am: 11.09.2015 15:57:01
Hallo Michael,
das ist auch super. Es funktioniert tadellos. Doch befürchte ich, dass es bei den Hunderttausenden von Datensätzen doch deutlich langsamer, als Rudis Version, sein wird, da Du ja jeden String Zeichen für Zeichen abklapperst. Aber für nicht ganz so viele DS ist es immer zu gebrauchen. Danke vielmals!
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Rudi Maintaire
Geschrieben am: 11.09.2015 12:32:01
Hallo,

Sub HebbettG()
  Dim s As String, a, i As Integer
  s = Range("A2").FormulaLocal
  a = Split(s, "!")
  For i = 1 To UBound(a)
  If Right(a(i - 1), 6) = "InputA" Then
  a(i) = Replace(Left(a(i), 2), "2", "3") & Mid(a(i), 3)
  End If
  Next
  s = Join(a, "!")
  MsgBox s
End Sub
Gruß
Rudi

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Gerd L
Geschrieben am: 11.09.2015 12:42:51
Hallo Rudi!
Das geht. Man kann Aufgaben einfach im eigenen Verständnis interpretieren.

Sub bb()
Dim strKette As String
strKette = Replace(Cells(2, 1).FormulaLocal, "InputA!A2", "InputA!A3")
MsgBox strKette
End Sub
Gruß Gerd

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 12:52:08
Hallo Gerd,
vielen Dank für Deine Lösung. Die ist so weit so gut. Doch was mache ich, wenn die Angaben in der Formel jedesmal anders sind und anstatt InputA!A2 nur noch InputA!B2 oder InputA!H2 kommt?
Rudis Vorschlag ist genau das, was ich gesucht habe! Aber trotzdem, vielen Dank für Deinen Vorschlag.
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 12:46:24
Hallo Rudiiiu,
kennst Du das höchste Lob, was Dir aus dem Munde eines Urbayern zu Teil werden kann? Nein? Dann sage ich es Dir jetzt: "A Hund bist fei scho!"!!! Kannst Du gerne recherchieren, doch das ist verbrieft und versiegelt!
In diesem Sinne: "A Hund bist fei scho!"!!! Und mercie vielmals!
Servus

Bild

Betrifft: Ergänzung
von: Rudi Maintaire
Geschrieben am: 11.09.2015 12:52:58
Hallo,
falls es auch mal um 2- und 3-buchstabige Zellen geht:

Sub HebbettG()
  Dim s As String, a1, a2, i
  s = Range("A2").FormulaLocal
  a1 = Split(s, "!")
  For i = 1 To UBound(a1)
  If Right(a1(i - 1), 6) = "InputA" Then
    a2 = Split(a1(i), ";")
    a2(0) = Replace(a2(0), "2", "3")
    a1(i) = Join(a2, ";")
  End If
  Next
  s = Join(a1, "!")
  MsgBox s
End Sub

Gruß
Rudi

Bild

Betrifft: AW: Ergänzung: Mercie nochmals! owt
von: Herbert Grom
Geschrieben am: 11.09.2015 13:01:13
,,

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 12:43:28
Hi
da musst du wohl in einer Schleife durch den Text gehen.
hier mal eine Variante für ein- und zweibuchtabige Spalten.
sollte deine Tabelle auf auf 3-Buchstabenspalten referenzieren, dann kannst du das ja entsrpechend ergänzen:

Sub test()
Dim FO As String
Dim i As Long
FO = Cells(1, 1).Formula
i = 1
Do
    i = InStr(i, FO, "InputA!")
If i = 0 Then Exit Do
    i = i + Len("InputA!")                          '--- Zeiger auf Adresse setzen
    If Mid(FO, i, 3) Like "[A-Z]2," Then            '--- Ein Spaltenbuchstabe
        Mid(FO, i + 1, 1) = "3"
    ElseIf Mid(FO, i, 4) Like "[A-Z][A-Z]2," Then   '--- Zwei Spaltenbuchstaben
        Mid(FO, i + 2, 1) = "3"
    End If
Loop
Cells(1, 1).Value = FO
        
End Sub
Gruß Daniel

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 12:56:14
Hallo Daniel,
vielen Dank für Deinen Lösungsvorschlag. Leider brachte er bei mir nicht das gewünschte Ergebnis. Aber macht ja nix, der gute Wille zählt. Außerdem ist Rudis Lösung genau das was ich suchte.
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 13:16:45
Hi
was funktioniert denn nicht?
was wäre denn dein gewünschtes Ergebnis und welches Ergebnis hat mein Makro anstatt geliefert?
falls du nur die Formeln auf deutsch brauchst, dann ersetze das ".Formula" durch ".FormualLocal"
und in den beiden LIKE-Funktionen das "," durch ";"
der eigentliche Unterschied zwischen Rudis und meinem Makro ist, das bei mir wirklich nur die Zeile 2 auf die Zeile 3 geändert wird, während bei Rudi auch die Zeilen 12, 22, 32, 42 usw zu 13, 33, 33, 43 usw würden, falls sie in der Formel den vorkommen.
Gruß Daniel

Bild

Betrifft: 12, 22, 32, 42 usw zu 13, 33, 33, 43
von: Rudi Maintaire
Geschrieben am: 11.09.2015 13:30:46
Hallo Daniel,
berechtigter Einwand.

Function HebbettG(s As String, strSheet As String, r1 As Long, r2 As Long)
  Dim a1, a2, i
  s = Range("A2").FormulaLocal
  a1 = Split(s, "!")
  For i = 1 To UBound(a1)
  If Right(a1(i - 1), 6) = strSheet Then
    a2 = Split(a1(i), ";")
    If Range(a2(0)).Row = r1 Then
      a2(0) = Cells(r2, Range(a2(0)).Column).Address(0, 0)
    End If
    a1(i) = Join(a2, ";")
  End If
  Next
  HebbettG = Join(a1, "!")
End Function

Sub test()
  MsgBox HebbettG(Range("A2").FormulaLocal, "InputA", 2, 3)
End Sub

Gruß
Rudi

Bild

Betrifft: AW: 12, 22, 32, 42 usw zu 13, 33, 33, 43
von: Herbert Grom
Geschrieben am: 11.09.2015 15:39:58
Hallo Rudi,
in meinem Falle ist das unerheblich, da die Formeln immer nur in Zeile 2 stehen!
So, und jetzt muss ich das Ganze noch für 26 Spalten in eine "For - Next"-Schleife reinpacken. Doch das kriege ich hin.
Aber da fällt mir doch noch eine Frage ein: Ich will die Formelergebnisse der jeweils geänderten Formeln in ein Array packen (kann bis zu 1 Million und mehr Datensätze werden) und das Array dann als csv-Datei speichern. Wie würdest Du das Array dimensionieren?
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 15:03:16
Hallo Daniel,
Rudis Code bringt mir diesen String = gewünschtes Ergebnis:
=VERKETTEN(InputA!A3;"||";InputB!F2;"||";InputA!B3;"||";InputB!G2;"||"; InputA!H3;"||";InputA!C3)
Dein Code bringt mir das: "Campaign"
Danke und Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 15:33:04
Hallo Daniel,
wenn ich bei Deinem Code aus "FO = Cells(1, 1).Formula" das mache: FO = "Cells(2, 1).Formula", dann klappt es auch mit Deinem Code. Danke noch mal!
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 16:16:05
Hallo Herbert
ok, verrate mir mal, woher ich das wissen hätte sollen, dass du die Formel in A2 stehen hast?
Bei VBA-Gut solltest du solche Kleinigkeiten selber anpassen können und nicht dem Antworter als fehlerhafte Lösung rückmelden.
Gruß Daniel

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 16:37:42
Hallo Daniel,
gaaanz ruhig bleiben, das war doch kein Vorwurf an Dich, sondern eher an mich, weil ich nicht richtig gekuckt habe. Sorry, das hätte ich beim 2. Mal noch dazu schreiben sollen! Selbstverständlich kann ich das selbst anpassen, aber dazu muss ich es eben erst mal sehen. Und ich habe es leider übersehen. Also noch mal, das war kein Vorwurf an Dich, sondern nur eine Feststellung. Und so wie es jetzt aussieht, werde ich sogar doch Deinen Code verwenden, denn ich brauche das Formelergebnis in meiner Variablen.
Allerdings gibt es noch ein Problem, bei der Fortschreibung der Zeilen. Ab 10 steigt er momentan aus. Also, die Ursprungsformel steht immer in der Tab "Output" in der Zeile 2. Die darin enthaltenen Formeln sollen aber fortgeschrieben werden, von Zeile 2 bis Irgendwas. Irgendwas errechnet sich aus den Zeilen in InputA * die Zeilen in InputB. Was müsste ich in Deinem Code anpassen, um auch noch Zeilen bis in die Million darstellen zu können?
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 17:04:16
Hi
probier mal


           If Mid(FO, i, 3) Like "[A-Z]2," Then            '--- Ein Spaltenbuchstabe
               FO = Left(FO, i) & "hier deine Zahl" & Mid(FO, i + 2)
mit Rudis zweitem Code sollte es auch problemlos funktionieren, durch das ersetzen jeden beliebigen Wert anstelle der 2 einzufügen.
allerding würde ich, wenn du da mit einer Schleife arbeitest vorab in der Formel die 2 durch ein anderes Zeichen ersetzen, welches sonst in der Formel nicht vorkommt (z.b. "_") und diesen Text in einer Zwischenvariable speichern.
dann kannst du in der Folge einfach mit der Replace-Funktion dieses Zeichen durch deine Zeilennummer ersetzen und musst nicht jedesmal durch den Code durch.
Gruß Daniel

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 17:59:16
Hallo Daniel,
vielen Dank, das klappt prima. Jedenfalls bis zur Zeile 9, dann wiederholt er sich dauernd. Schau mal:
0011||Überschrift 1||xyz GmbH||Text Zeile 1||Test Aktion||AUG15
0011||Überschrift 2||xyz GmbH||Text Zeile 2||Test Aktion||AUG15
0011||Überschrift 3||xyz GmbH||Text Zeile 3||Test Aktion||AUG15
0011||Überschrift 4||xyz GmbH||Text Zeile 4||Test Aktion||AUG15
0011||Überschrift 5||xyz GmbH||Text Zeile 5||Test Aktion||AUG15
0011||Überschrift 6||xyz GmbH||Text Zeile 6||Test Aktion||AUG15
0011||Überschrift 7||xyz GmbH||Text Zeile 7||Test Aktion||AUG15
0011||Überschrift 8||xyz GmbH||Text Zeile 8||Test Aktion||AUG15
0011||Überschrift 9||xyz GmbH||Text Zeile 9||Test Aktion||AUG15
0011||Überschrift 9||xyz GmbH||Text Zeile 9||Test Aktion||AUG15
0011||Überschrift 9||xyz GmbH||Text Zeile 9||Test Aktion||AUG15

Und ich finde nicht, wo ich das erweitern kann. Vor allem kann es ja auch bis in die Sechsstelligkeit gehen. Kannst Du mir da noch bitte helfen?
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 18:02:13
Hallo Herbert
du solltest mittlerweile wissen, dass ich, um dir helfen zu können, wissen muss wie dein Code und die dazugehörige Tabelle aussieht.
Gruß Daniel

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 18:21:09
Hallo Daniel,
OK, ich lade die Datei hoch. In ein paar Minuten ist es so weit.
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 18:44:39
Hi Daniel,
anbei nun meine Beispieldatei. Vielen Dank im Voraus.
https://www.herber.de/bbs/user/100144.xlsm
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 19:59:04
Hi
ich glaube nicht dass man das programmieren muss.
du kannst doch die Liste in der Tabelle mit Formeln erstellen und dann die ganze Tabelle als CSV speichern.
Im Prinzip so
für InputB:
Index(InputB!A:A;Rest(Zeile()-2;AnzahlWerte in InputB)+2)
für InputA:
Index(InputA!A:A;Ganzzahl((Zeile()-2)/AnzahlWerte in InputB)+2)
damit ab Zeile 2 die Tabelle bis zum Ende füllen und dann Tabelle als ganzes als CSV speichern.
Gruß Daniel

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 11.09.2015 20:17:49
Hi Daniel,
das mit den Formeln haben wir ja schon. Doch damit läuft das Teil an die 20 Minuten! Deshalb wollen wir es mit VBA probieren. Das ist der Grund.
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 11.09.2015 20:35:22
dann lies doch die Daten komplett in zwei Arrays ein und kombiniere dann über zwei geschachtelte Schleifen der beiden Arrays.
Das sollte dann schneller sein als jedesmal den Wert aus den Tabellen zu lesen.
Gruß Daniel

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Herbert Grom
Geschrieben am: 12.09.2015 10:03:44
Hallo Daniel,
das mit dem einlesen der Daten in zwei Arrays ist ein super Vorschlag, daran habe ich noch gar nicht gedacht! Das einlesen würde ich ja noch hinbekommen, aber das kombinieren der beiden Arrays ist mir nicht geläufig. Kannst Du mir dabei bitte noch helfen?
Servus

Bild

Betrifft: AW: Formel aus Zelle in VBA einlesen und ändern
von: Daniel
Geschrieben am: 12.09.2015 15:25:17
Hi
im Prinzip so:

ReDim arr3(1 To (UBound(arr1) + 1) * (UBound(arr2) + 1), 1)
For x = 0 To UBound(arr1)
    For y = 0 To UBound(arr2)
        z = z + 1
        arr3(z, 1) = arr1(x) & arr2(y)
    Next
Next
bei der Berechnung der Anzahl der Elemente in den Quell- und im Zielarray musst du immer beachten, ob du ein 0- oder ein 1-basiertes Array hast.
ich habe hier im Beispiel für die Quelle zwei normale eindimensionale 0-basierte Arrays verwendet, für das ZielArray jedoch ein 1-basiertes zweidimensionales, weil man das besser in die Zellen zurückschreiben kann.
Gruss Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formel aus Zelle in VBA einlesen und ändern"