Office: (Office 2016) Per VBA Formel kopieren und Bezug ersetzen

Hallo zusammen,

ich habe da ein etwas verzwicktes Problem, für das ich keine ordentliche Lösung finde.

Ich lese aus zwei Blättern Daten in Datenfelder "ProcessOrders" und "Standard". Dabei lese ich im Datenfeld "Standard" u.a. wieder ein Datenfeld mit Formeln ein.

Für alle Daten aus dem Feld "ProcessOrders" durchsuche ich das Feld "Standard" nach Übereinstimmungen und erstelle daraus das Datenfeld "Liste", das ich anschließend im Blatt "Ergebnis" ausgebe.

Das funktioniert gut, außer dass Teile der Formel anschließend angepasst werden müssen, da natürlich die Bezüge beim Kopieren beibehalten werden.
Als Beispiel:
Im Blatt "Standard" Zelle F2 lautet die Formel: =SUMME(VSMneu!$E$2:$J$2)+D2*SUMME(VSMneu!$E$3:$J$3)
Im Blatt "Ergebnis" Zelle T2 muss sie lauten:=SUMME(VSMneu!$E$2:$J$2)+P2*SUMME(VSMneu!$E$3:$J$3)

Ich habe mich mit folgendem Workaround beholfen:
Die Zelle D2 bekommt im Blatt "Standard" einen Namen (M_02_X), den ich im Anschluss per Makro suche und durch den Zellbezug ersetze (hier nur die Testversion).

Code:

Formel = ActiveCell.Formula
pos1 = InStr(Formel, "M_")
pos2 = InStr(pos1 + 1, Formel, "_X")

If pos2 - pos1 = 4 Then

    VTeil = Left(Formel, pos1 - 1)
    HTeil = Right(Formel, Len(Formel) - pos2 - 1)
    MTeil = Cells(ActiveCell.Row, 16).Address

    ActiveCell.Formula = VTeil & MTeil & HTeil

End If

Ich finde den Weg aber etwas schwierig. Hat jemand eine Idee, wie es besser geht?
Den Anhang 9575 betrachten

Vielen Dank für Eure Hilfe,
Lutz

 

Hallo,

warum verwendest du nicht für die vorhin zitierte Code-Passage folgenden VBA-Code:

Code:

  With ActiveCell
    .Formula = Replace(.Formula, "M_02_X", Cells(.Row, 16).Address)
  End With

Dieser bewirkt exakt dasselbe.

 

Hallo Anton,

erstmal vielen Dank für Deine Hilfe.
Das Problem lag darin, dass die beiden Ziffern immer unterschiedlich waren.
Und mit

Code:

  With ActiveCell
    .Formula = Replace(.Formula, "M_*_X", Cells(.Row, 16).Address)
  End With

funktioniert's nicht.

Hab jetzt aber festgestellt, dass ich für den Bereich der SpalteD einen Namen vergeben kann und diesen anschließend wie von dir beschrieben umbenennen kann.

Vielen Dank,
Lutz

 

Hallo,

wo liegt hier das Problem? Z.B. kann man mit folgendem Code mittels Integer-Variablen I die Suchvariable flexibel abändern:
Falls I=3 ist, erhält man "M_03_X" als Suchvariable, etc.

Code:

  Dim I As Integer
' ....
' ....
  With ActiveCell
    .Formula = Replace(.Formula, "M_" & Format(I, "00") & "_X", Cells(.Row, 16).Address)
  End With

 

Hallo Lutz,

wenn du sogenannte "Reguläre Ausdrücke" einsetzt, ist das Ganze im Kern noch einfacher

Code:

[B][COLOR="#0000CD"]  Dim RgEx As Object[/COLOR][/B]
  [B][COLOR="#0000CD"]Set RgEx = CreateObject("VBScript.RegExp")[/COLOR][/B]
'...
  [B][COLOR="#0000CD"]RgEx.Pattern = [/COLOR][COLOR="#FF0000"]"M_.+?_X"[/COLOR][/B]
'...
'...
  With ActiveCell
    .Formula = [B][COLOR="#0000CD"]RgEx.[/COLOR][/B]Replace(.Formula, Cells(.Row, 16).Address)
  End With
'...
'...
  Set RgEx = Nothing

1) Es wird hier mittels "CreateObject(...)" die Objekt-Variable "RgEx" erzeugt.
2) Der Eigenschaft "Pattern" von "RgEx" wird das Suchmuster "M_.+?_X" zugewiesen.
3) Dann kann beliebig oft die "Replace(Ausdruck,Wert)"-Methode der "RgEx"-Variable ausgeführt werden.
Diese Methode sucht das Pattern-Muster im Parameter "Ausdruck" und, falls dieses gefunden wurde, wird es durch den Parameter "Wert" ersetzt.
Ich habe dir die Pattern-Eigenschaft so eingestellt, dass "Replace(...)" in ".Formula" nach der 1. Stelle sucht, die mit "M_" beginnt und mit "_X" endet, wobei dazwischen mindestens 1 Zeichen sein muss.
Wird eine solche Zeichenfolge gefunden, wird diese durch den Inhalt von "Cells(.Row,16).Address" ersetzt.

Falls du jedoch mehrere solche Fundstellen in ein u. demselben ".Formula"-Ausdruck ersetzen möchtest durch den "Cells.."-Ausdruck, müsstest du irgendwo vor der Zeile mit "RgEx.Replace(...)" - am besten unmittelbar nach der Zeile "RgEx.Pattern..." - folgende Zeile einfügen:
RgEx.Global = True

 

Hallo Anton,

ich versteh nur Bahnhof, aber es funktioniert.

Besten Dank,
Lutz

 

Hallo Anton,

ich habe nochmal ein wenig an dem Problem gebastelt.

Ich habe jetzt in allen fraglichen Zellen den bezug auf die benannte Zelle "Menge" stehen.
z.B. =SUMME(VSMneu!$E$23:$J$23)+Menge*SUMME(VSMneu!$E$24:$J$24)

Mit folgendem Code funktionierts einwandfrei:

Code:

With ActiveCell
   .Formula = Replace(.Formula, "Menge", Cells(.Row, 16).Address)
End With

Jetzt hab ich meine Bearbeitung in Datenfelder gelegt und schon funktioniert's nicht mehr.

Code:

For Spalte = 1 To SpaltenStandards
        With Liste(Zeile, 19)(1, Spalte)
            .Value = Replace(.Value, "Menge", Cells(Zeile + 1, 16).Address)
        End With
    Next Spalte
    Range(Cells(Zeile + 1, 19), Cells(Zeile + 1, 19 + SpaltenStandards)) = Liste(Zeile, 19)

Lasse ich den Code in Einzelschritten durchlaufen, zeigt er mir solange er in Zeile 2 des Codes steht und ich mit der Maus auf den Ausdruck "Liste(Zeile, 19)(1, Spalte)" gehe, den aktuellen Inhalt an des Feldes an. Steht er in Zeile 3 des Codes, zeigt er mir für den Ausdruck "Liste(Zeile, 19)(1, Spalte)" =Leer an.
Anschließend erhalte ich Laufzeitfehler 424 (Objekt erforderlich).

Wo liegt mein Fehler?

Gruß,
Lutz

 

Hallo Lutz,

du verschachtelst in einem FOR-NEXT-Schleifenblock einen WITH-ENDWITH-Block. Dieser sieht folgendermaßen aus:

Code:

With [COLOR="#FF0000"]Liste(Zeile, 19)(1, Spalte)[/COLOR]
    [COLOR="#FF0000"][B].[/B][/COLOR]Value = Replace([COLOR="#FF0000"][B].[/B][/COLOR]Value, "Menge", Cells(Zeile + 1, 16).Address)
End With

Im WITH-Block verwendest zweimal du den Punkt-Operator, ohne davor eine Objektinstanz hinzuschreiben, was in Visual-Basic soviel heißt wie: "Nimm den Ausdruck, der auf WITH folgt, und füge ihn unmittelbar vor diesen beiden Punkten ein".
D.h. schreibt man vorigen Code ohne WITH/END-WITH, sieht er folgendermaßen aus:

Code:

[COLOR="#FF0000"]Liste(Zeile, 19)(1, Spalte)[/COLOR].Value = Replace([COLOR="#FF0000"]Liste(Zeile, 19)(1, Spalte)[/COLOR].Value, "Menge", Cells(Zeile + 1, 16).Address)

Der Compiler erkennt aufgrund des vorhandenen Punktes vor "Value", dass "Value" eine Eigenschaft (bzw. eine Methode) eines Objektes sein muss. Er kann jedoch offenbar mit dem, was ihm im Code vor diesem Punkt angeboten wird, nichts anfangen, zumindest ist es keine Objektinstanz im Sinne der "objektorientierten Programmierung". Da ich die vorausgehende explizite (bzw. implizite) Deklaration von "Liste" nicht kenne, kann ich dazu nicht viel sagen - es sieht so aus, als wäre "Liste" ein zweidimensionales Array, in dessen Elementen wieder je ein zweidimensionales Array enthalten ist.

Im ersten Codebeispiel, das du anführst, steht im Gegensatz dazu nach WITH "ActiveCell", sodass nach Entfernung der WITH/END-WITH Blockung dort zu stehen käme:

Code:

[COLOR="#FF0000"]ActiveCell[/COLOR].Formula = Replace([COLOR="#FF0000"]ActiveCell[/COLOR].Formula, "Menge", Cells([COLOR="#FF0000"]ActiveCell[/COLOR].Row, 16).Address)

Da "ActiveCell" ein Range-Objekt ist und ein Range-Objekt so definiert ist, dass es eine "Formula"-Eigenschaft und eine "Row"-Eigenschaft hat, funktioniert dieser Code.

 

Hallo Anton,

na logisch.
Hätte ich auch selbst drauf kommen können.

Mit

Code:

For Spalte = Loeschen * 3 + 1 To SpaltenStandards
    Liste(Daten, 19)(1, Spalte) = Replace(Liste(Daten, 19)(1, Spalte), "Menge", Cells(Daten + 1, 16).Address)
Next Spalte

funktioniert's.

Vielen Dank für die Hilfe,
Lutz