VBA: Nach bestimmten Formeln suchen und Inhalte einfügen

[Crimson_Sabbath]

Hallo zusammen,

ich hätte mal wieder eine Fragestellung die sicherlich einfach ist wenn man die Lösung kennt...

Ich habe ein größeres Makro geschrieben, soweit funktioniert auch alles. Nur ein kleines Problem besteht:

Problemstellung: Im aktivierten Arbeitsblatt (gefühlt die Nach durchgearbeitet, was ist hierfür ein umgangssprachlicher Name? :/) soll nach bestimmten Formeln gesucht werden (bspw. =Wenn). In allen Zellen die eine solche Formel enthalten, sollen die Formeln durch Werte ersetzt werden.

Dem geübten Auge wird ggfs. auffallen das ich mir hier etwas zusammenkopiert habe weil mir die Syntax in diesem Falle etwas schwer fällt.

Ich habe es mal in eine separate Datei gepackt um es losgelöst vom restlichen Makro zu testen:

Private Sub CommandButton1_Click()

          For Each rngCell In ActiveSheet.UsedRange
            If Left(rngCell.Formula, 3) = "=IF" Then
            ActiveCell.PasteSpecial Paste:=xlPasteValues
            End If
        Next
End Sub

Wo liegen die Fehler?

P.s.: Rein vom Verständnis: Wenn ich das Makro so aufbaue, wie viele Zellen geht Excel bei der Prüfung dann durch? Nur die Zellen in denen auch ein Wert / Formel ist? Oder alle Zellen (was in die Millionen geht)

[DIZA]

Private Sub CommandButton1_Click()

          For Each rngCell In ActiveSheet.UsedRange
            If Left(rngCell.Formula, 3) = "=IF" Then
            ActiveCell.PasteSpecial Paste:=xlPasteValues
            End If
        Next
End Sub


P.s.: Rein vom Verständnis: Wenn ich das Makro so aufbaue, wie viele Zellen geht Excel bei der Prüfung dann durch? Nur die Zellen in denen auch ein Wert / Formel ist? Oder alle Zellen (was in die Millionen geht)

Die Datei hättest du mal mitschicken sollen.
So wie du es "ansprichst" durchläuft das Makro alle Zellen die in dem benutzten Bereich sind, wobei hier Vorsicht geboten ist, da der Bereich gerne deutlich größer sein kann als du ihn meinst zu sehen. Hier würde sich eine klare Abgrenzung anbieten/empfehlen.

---

Dann fehlen noch einige Angaben.
welche Formeln / und vor Allem was soll in den Formeln durch was ersetzt werden ?

[Crimson_Sabbath]

Hallo,

danke für die schnelle Antwort.

Und korrekt: Ich hätte die Datei besser direkt mitgeschickt. Das macht es wohl einfacher.
Ich habe sie angehängt.

[DIZA]

oh oh, die Datei ist fehlerhaft.
egal den Sinn dahinter konnte ich erkennen, jedoch ist ja immer noch nicht geklärt was du gegen was tauschen möchtest.

Vom Ansatz her, vlt. hilfts dir ja schon, .....

Code:

    Columns("F:F").Replace What:="=WENN(", Replacement:="=WENN(", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

---

PHP-Code:

Private Sub CommandButton1_Click()
   With Columns("F:F")
      .Replace What:="=WENN(", Replacement:="=WENN(", LookAt:=xlPart, _
              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
              ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
   End With
End Sub 


[Crimson_Sabbath]

Was ein vernichtendes Urteil ;).

Aber gerechtfertigt.

Mir geht es "einfach" darum: Such nach bestimmten Formeln (bspw. alle "Wenn"-Funktionen) in dem aktiven Arbeitsblatt und ersetz mir die Formeln durch feste Werte.

Eigentlich nicht so schwer, aber irgendwas stellt sich hier in meinem Kopf queer

[DIZA]

OK,, ein weitere Ansatz

Code:

Sub ersetzen()
Dim Bereich As String, ges As String, ers As String, Zelle
Bereich = "F4:F25"                                    ' hier legst du den Bereich fest in dem getauscht werden soll / das sollte besten Falls durch letzte Spalte und letzte Zeile definiert werden
ges = "=WENN"                                         ' nach was suchst du
ers = "Da war mal Wenn da"                            ' gegen was soll getauscht werden
For Each Zelle In Range(Bereich)
   If InStr(Zelle.FormulaLocal, ges) > 0 Then
      Zelle.Value = ers
   End If
Next
End Sub

[HKindler]

Hi,

ich würde das so machen:

Code:

Dim Zelle As Range
Dim Bereich As Range
Set Bereich = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
For Each Zelle In Bereich
    If Zelle.FormulaLocal Like "=WENN(*" Then
        Zelle.Value = Zelle.Value
    End If
Next Zelle

Hier werden alle Zellen mit Formeln untersucht. Wenn die Formel mit =WENN( beginnt, dann wird sie durch ihr Ergebnis ersetzt.

Zu deiner Frage, was du falsch machst:
- wenn du mit .PasteSpecial etwas einfügen willst, dann musst du vorher etwas kopieren
- wenn du ActiveCell verwendest, solltest du dafür sorgen, dass die gewünschte Zelle auch aktiv ist (NEIN! Tu das nicht, sondern verwende nicht ActiveCell, sondern in deinem Fall rngCell)

Korrekt (im Sinne von: es macht was es soll) würde dein Code so lauten:

Code:

For Each rngCell In ActiveSheet.UsedRange
            If Left(rngCell.Formula, 3) = "=IF" Then
                rngCell.Copy
                rngCell.PasteSpecial Paste:=xlPasteValues
            End If
        Next

[snb]

Code:

Sub M_snb()
  For Each it In ActiveSheet.UsedRange.SpecialCells(-4123)
    If InStr(it.Formula, "=IF") Then it.Value = it
  Next
End Sub

[Crimson_Sabbath]

Ich bin immer wieder überrascht wie viele Wege eigentlich nach Rom führen. Und wie einfach doch eigentlich guter VBA-Code ist wenn man ihn liest.

Die Hilfestellung hat mir sehr weitergeholfen. Vielen Dank an euch alle