Anzeige
Archiv - Navigation
1608to1612
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Text in nFormeln ersetzen und zählen

Text in nFormeln ersetzen und zählen
26.02.2018 19:48:39
Michael
Hallo,
ich möchte gerne ca. 500 Bezüge in Formeln mehrerer Blätter und Zellen ersetzen.
Meine Frage:
Gibt es eine Möglichkeit die Ersetzungen zu zählen und dabei meine ich nicht die Formeln zu zählen in denen ersetzt wurde, sondern wirklich jeden einzelnen Bezug, auch wenn mehrere Bezüge in einer Formel sind?
Für einen kleinen Beispielcode wäre ich sehr dankbar.
Gruß
Micha

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Text in nFormeln ersetzen und zählen
27.02.2018 03:39:27
Rainer
Hallo Micha,
Benutze "Suchen und Ersetzen".
Zuerst ersetzt du alle "=" durch "XXX". Damit werden deine Formeln zu Texten.
Dann kannst du wieder über "Suchen und Ersetzen" deine Bezüge anpassen. Am Ende gibt dir eine Meldung die Zahl der Ersetzungen an.
Abschließend wieder alle "XXX" durch "=" ersetzen und fertig.
Gruß,
Rainer
AW: Text in nFormeln ersetzen und zählen
27.02.2018 07:13:35
Michael
Hallo Rainer,
vielen Dank für deine Antwort. Leider hab ich Dussel vergessen zu erwähnen, dass ich das ganze in VBA mit einem Makro realisieren muss.
Vielleicht hat noch jemand eine Idee.
Gruß
Micha
AW: Text in nFormeln ersetzen und zählen
27.02.2018 07:20:14
Rainer
Hallo Micha,
du hast angegeben "VBA nein"!
Aber schau mal das hier an:
Option Explicit
Sub fndReplace()
Dim i As Long
Dim rng As Range
Dim c As Range
Set rng = ActiveSheet.UsedRange 'saves searching whole sheet
i = 0
For Each c In rng
c.Replace What:=ActiveSheet.Name & "A", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i = i + 1 'counts changes
Next c
MsgBox i
End Sub
Gefunden hier nach 15 Sekunden Recherche: https://www.ozgrid.com/forum/forum/help-forums/excel-general/18117-count-replacements-in-vba
Gruß,
Rainer
Anzeige
das VBA nein...
27.02.2018 07:27:16
Bernd
Moin,
bezieht sich ja auf seine Kenntnisse, nicht auf die Lösung.
Im ersten beitrag schrieb er ja:
Für einen kleinen Beispielcode wäre ich sehr dankbar.
MfG Bernd
AW: Text in nFormeln ersetzen und zählen
27.02.2018 15:05:55
Michael
Hallo Rainer,
danke für den Code, so einen ähnlichen habe ich bereits getestet und er zählt leider nur die Anzahl der Zellen in denen der Text geändert wurde. Ich benötige jedoch die Anzahl der Ersetzungen insgesamt, also auch wenn mehrere Ersetzungen in der selben Zelle getätigt wurden.
Vielleicht gibt es ja dafür doch noch eine Lösung.
Gruß
Micha
AW: Text in nFormeln ersetzen und zählen
27.02.2018 15:41:42
Rainer
Dann erweitere es so:

Sub fndReplace()
Dim i As Long
Dim rng As Range
Dim c As Range
Set rng = ActiveSheet.UsedRange 'saves searching whole sheet
i = 0
  l = 0       'Das ist der Zähler
For Each c In rng
SW = c.value  'habs nicht probiert, evtl. anpassen, muss ein String sein
k = 1
While Err.Number = 0
On Error Resume Next
m = WorksheetFunction.Find("a", SW, k)
If Err.Number = 0 Then l = l + 1
k = m + 1
Wend
Err.Clear
c.Replace What:=ActiveSheet.Name & "A", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i = i + 1 'counts changes
Next c
MsgBox i
End Sub

Anzeige
AW: Text in nFormeln ersetzen und zählen
27.02.2018 20:25:02
Michael
Hallo Rainer,
vielen Dank für deine Antwort, habe sie leider erst jetzt gelesen, war selber beschäftigt den Code zu basteln. Deinen Code habe ich jetzt nicht mehr getestet, da meiner super funktioniert.
Für alle die es interessiert, dieser Beispielcode überprüft in Formeln die Bezüge zu anderen Dateien und ersetzt sie mit dem Ersatzbezug. Jede Ersetzung wird dabei gezählt.
Sub Bezüge_ersetzen()
Dim Suchbezug As String 'der jeweilige Bezug in einer Formel
Dim Ersatzbezug As String 'Ersatzbezug
Dim Text As String
Dim alt As String, alt1 As String, alt2 As String, alt3 As String
Dim Bereich As Range, rng As Range
Dim i As Integer 'Position in Text um nach dem nächsten Bezug zu suchen
Dim t As Long 'Anzahl der Bezüge die ersetzt wurden
'Ersatzbezug festlegen
Ersatzbezug = "'C:\Eigene Dateien\[Test.xlsm]Tabelle1'!$A$1"
'zu suchenden Bereich festlegen
Set Bereich = Sheets("Tabelle1").Range("A1:B20")
'Zähler auf Null setzen
t = 0
'Bezüge ersetzen und Ersetzungen zählen
For Each rng In Bereich
i = 1
Do
Text = rng.Formula 'Formel aus der jeweiligen Zelle als Referenzbezug alt
alt1 = InStr(i, Text, "]") 'Position des Bezugende festlegen
alt2 = Left(Text, alt1) 'linken Abschnitt der Formel bis zur Klammer
alt3 = InStr(i, alt2, "'") 'Position des ' in dem Formelabschnitt festlegen
If alt1 = 0 Then Exit Do
If alt1  0 Then Suchbezug = Mid(alt2, alt3) 'Suchbezug aus der Formel
i = InStr(i, Text, "'!", vbTextCompare) + 2
If Suchbezug  Ersatzbezug Then
If InStr(1, Text, Suchbezug, vbTextCompare) > 0 Then
t = t + 1
rng.Value = Replace(Text, Suchbezug, Ersatzbezug, 1, 1, vbTextCompare)
End If
End If
Loop Until (InStr(1, Text, Suchbezug, vbTextCompare) = 0)
Next
MsgBox t
End Sub
Grüße
Micha
Anzeige
AW: Text in nFormeln ersetzen und zählen
28.02.2018 03:22:11
Rainer
Glückwunsch, für deinen VBA-Level ("nein") ist das eine super Leistung.
Denke bitte in Zukunft daran, dass
1. so ein Forum freiwillig ist,
2. die Leute hier kostenlos in ihrer Freizeit helfen und
3. die allermeisten nicht gern für den Papierkorb arbeiten.

367 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige