Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
19.04.2024 15:39:42
Exceltabelle daten per combobox zu word Datei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1704to1708
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1704to1708
1704to1708
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

x in = ändern

x in = ändern
26.07.2019 11:12:30
Michael
Hallo
Habe mir einen Code zusammengebastelt, komme nun aber nicht weiter. In einfachen Worten: der Code soll alle Sheets in einer Mappe ansprechen deren Name mit "Ü" beginnt(ZB:Ü001,Ü018,Ü339 usw.), und in diesen Sheets in Spalte H, Zeilen 3-444 das voranstehende "x" im Text mit einem "=" ersetzen.
  • 
Sub x_ändern()
Application.ScreenUpdating = False
Dim I As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then
With ws
For I = 3 To 444
Cells(I, 8).FormulaLocal = Replace(Cells(I, 8), "x", "=") 'Index 8= Spalte H
Next I
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

    • In der Zeile
  • Cells(I, 8).FormulaLocal = Replace(Cells(I, 8), "x", "=")

    • bleibt mein Code stehen, und Excel sagt "1004" Anwendungs oder Objekt orientierter Fehler
    Wenn ich folgenden Code für jeden Sheet einzeln schreibe, funktioniert es aber.
  • 
Sub Ersetze()
Application.ScreenUpdating = False
Dim I As Long
For I = 3 To 444
Sheets("Ü001").Cells(I, 8).FormulaLocal = Replace(Cells(I, 8), "x", "=")
Next I
Application.ScreenUpdating = True
End Sub

    • Kann mir bitte jemand weiterhelfen. Vielen Dank und Gruß
    Michael

    20
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: x in = ändern
    26.07.2019 11:19:35
    peterk
    Hallo
    Du hast die Punkte vergessen (damit die Zelle der With Anweisung genommen wird und nicht die des aktiven Balttes!)
    
.Cells(I, 8).FormulaLocal = Replace(.Cells(I, 8), "x", "=") 'Index 8= Spalte H

    AW: x in = ändern
    26.07.2019 11:29:04
    Michael
    Hallo peterk
    Danke für deine schnelle Antwort. Leider funktioniert es nicht. Habe die beiden Punkte gesetzt, aber es kommt dieselbe Fehlermeldung. Auch dann wenn ich nur den ersten Punkt setze. Da muss noch ein anderer Fehler sein.
    Gruß michael
    AW: Blattschutz
    26.07.2019 11:38:27
    hary
    Moin
    Hast du Blaetter geschuetzt?
    gruss hary
    AW: Blattschutz
    26.07.2019 11:59:10
    Michael
    Hallo Hary
    Die Blätter sind nicht Schreibgeschützt.
    Gruß Michael
    Anzeige
    AW: Blattschutz
    26.07.2019 12:06:26
    Werner
    Hallo Michael,
    offensichtlich geht es dir ja wohl darum in der Spalte H eingetragene Formeln, die mit dem x am Anfang "unscharf" sind als "scharf" zu setzen.
    Wie schon angemerkt, würde ich da nicht auf einen Buchstaben zurückgreifen, da du dir sonst alle vorhandene Buchstaben ersetzt, außer du prüfst dann auch noch, ob dieser Buchstabe das erste Zeichen ist.
    Ich würde da eher auf Zeichen zurückgreifen, die ansonsten gesichert nicht noch einmal vorkommen können, z.B.: ##
    Dann kannst du mit Excel Funktion Suchen/Ersetzen arbeiten und sparst dir die Schleife über alle Zeilen. 
    Sub x_ändern()
Dim I As Long, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then
With ws
.Range("H3:H444").Replace What:="##", Replacement:="="
End With
End If
Next ws
End Sub
    Gruß Werner
    Anzeige
    AW: Blattschutz
    26.07.2019 12:46:36
    Michael
    Hallo Werner
    Habe deinen Vorschlag ausprobiert, aber es passiert Garnichts. Keine Fehlermeldung, aber auch kein ändern. ?
    "Application.ScreenUpdating = True" habe ich noch am Ende dazu geschrieben, hat's aber auch nicht gebracht.
    Gruß Michael
    AW: Blattschutz
    26.07.2019 13:44:29
    Werner
    Hallo Michael,
    dann lad mal deine Datei hier hoch, sonst wird das nur eine Raterei.
    Gruß Werner
    AW: Blattschutz
    26.07.2019 15:26:42
    Michael
    Hallo Werner
    Hier die gewünschte Mappe
    https://www.herber.de/bbs/user/131102.xlsm
    Die Original Mappe umfasst ca. 70 Sheets, wovon 31 mit "Ü" beginnen.
    In der Testmappe läuft alles wesentlich schneller ab. Kann das an der Datei, gesamt Größe liegen?
    Das Original hat 6100kb, Tendenz steigend.
    Gruß
    Michael
    Anzeige
    AW: Blattschutz
    26.07.2019 15:55:49
    Daniel
    Hi
    keine Ahnung, warum das mit dem Replace hier nicht klappt.
    die andere schnelle alternative wäre, die Texte zunächst in ein Array zu laden, dort die Umwandlung durchzuführen und dann das Array als ganzes zurückzuschreiben.
    dann kannst du auch bei den deutschen Formeln bleiben.
    für die Replace-Variante musst du amerikanische Formeltexte verwenden:
    Sub x_ändern_NEU()
Dim ws As Worksheet
Dim arr
Dim z As Long
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then
With ws.Range("H3:H444")
arr = .Value
For z = 1 To UBound(arr)
arr(z, 1) = "=" & Mid(arr(z, 1), 3)
Next
.FormulaLocal = arr
End With
End If
Next
Application.ScreenUpdating = True
End Sub

    ich habe das jetzt so ausleget, dass die ersten zwei Zeichen am Anfang durch ein "=" ersetzt werden.
    dies ggf an deine Datei angleichen.
    Gruß Daniel
    Anzeige
    AW: Blattschutz
    26.07.2019 16:33:02
    Michael
    Hallo Daniel
    Zitat:ich habe das jetzt so ausleget, dass die ersten zwei Zeichen am Anfang durch ein "=" ersetzt werden.
    Ist Korrekt, nach dem Hinweis das das x auch an anderer Stelle in den Formeln sein kann, bin ich auf doppelt x gegangen.
    Habe deinen neuen Code gerade in der Original Mappe ausprobiert: Läuft super, schnell und sauber, durch. Hat kaum 2 Sekunden für alle 31 Sheets gebraucht.
    Vielen Dank dafür
    Gruß
    Michael
    AW: x in = ändern
    26.07.2019 11:40:22
    Daniel
    HI
    das mit den Punkten wurde ja schon gesagt.
    ich würde hier aber nicht mit Replace arbeiten.
    es wäre ja möglich, dass innerhalb der Formel nochmal ein "x" vorkommt und wenn du dort das "=" einbaust, hast du keine funktionierende Formel mehr:
    ich würde den Code so schreiben und das "x" wirklich nur am Anfang ersetzen:
    for each WS in ThisWorkbook.Worksheets
if WS.Name like "Ü*" then
for i = 3 to 444
with WS.Cells(i, 8)
if .Value like "x*" then .FormulaLocal = "=" & Mid(.Value, 2)
end with
next i
end if
next WS
    wenn der Code mit einem Fehler stoppt, solltest du dir den Text in der Zelle, die gerade bearbeitet wird, genauer anschauten und überprüfen, ob diese in der angegebenen Schreibweise eine korrekte Formel ergibt.
    Gruß Daniel
    Anzeige
    AW: x in = ändern
    26.07.2019 12:40:01
    Michael
    Hallo Daniel
    In den Formeln kommt kein weiteres x vor, aber der Hinweis dazu und die Änderung ist sinnvoll.
    Kannst du mir erklären was der Part "& Mid(.Value, 2)" aussagt?
    Dein Code funktioniert zuverlässig, bei 31 Reitern x 441 Zeilen, kann man aber eine Zigarettenpause einlegen bis er fertig ist.(KEINE Kritik)
    Kann man das noch irgendwie beschleunigen?
    Gruß Michael
    AW: x in = ändern
    26.07.2019 12:59:53
    Daniel
    Hi
    naja, damit setze ich um, was ich erwähnt habe.
    ich nehme das Zeichen ("x") am Anfang weg und tausche es durch das "=" aus.
    Mid(...,2) ist der angegebene Text ab dem 2. Zeichen.
    wenn in den Formeln das "x" nur am Anfang vorkommt, wäre der sinnvollste Weg zur Beschleunigung so:
    erstelle die Formeltexte in Amerikanischer Schreibweise, dh englische Funktionsnamen und Komma als Parametertrennzeichen.
    Wenn beides gegeben ist, kannst du die Schleife über die Zellen durch ein einziges Ersetzen/Replace austauschen und so alle Zellen auf dem Blatt in einem Schritt umwandeln (man nutzt hier die Menüfunktion ERSETZEN): 
    For each WS in Thisworkbook.Worksheets
if WS.Name like "Ü*" Then WS.Range("H3:H444").Replace "x", "=", xlpart
Next
    die Tücke hierbei ist, dass wenn die Aktion von Excel ausgeführt, die Formel im amerikanischer Schreibweise stehen muss und wenn du das von Hand machst, in deutscher.
    ansonsten Helfen die üblichen GetMoreSpeed-Einstellungen:
    am Anfang: 
    With Application
.ScreenUpdating = false
.Calculation = xlcalculationmanual
.EnableEvents = False
End with

    und am Schluss setzt man die Einstellungen wieder zurück: 
    With Application
.ScreenUpdating = true
.Calculation = xlcalculationautomatic
.EnableEvents = true
End with
    wobei man da noch folgendes beachten muss, wenn beim Testen ein Fehler auftritt und die Einstellungen nicht per Code wieder zurückgesetzt werden:
    - für screenupdating: egal, das wird automatisch wieder aktiviert
    - für Calculation: die kann man in dem Menüpunkt "Formeln - Berechnungsoptionen" wieder aktivieren
    - für EnableEvents: die muss man über das DIREKTFENSTER wieder einschalten und dort den Befehl eingeben.
    Gruß Daniel
    Anzeige
    AW: x in = ändern
    26.07.2019 13:42:55
    Michael
    Hallo Daniel
    Habe den Code folgender Maßen ausprobiert:
  • 
Sub x_ändern_NEU()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then ws.Range("H3:H444").Replace "x", "=", xlPart
Next
Application.ScreenUpdating = True
End Sub

    • Wahrscheinlich habe ich aber was falsch verstanden, jetzt passiert nichts mehr. Keine Fehlermeldung aber auch kein ersetzen. Gibt es irgendwelche Grundeinstellungen im Editor, wo bei mir der Haken fehlt oder zu viel ist?
    Gruß Michael
    ganz anderer Ansatz
    26.07.2019 16:51:39
    Daniel
    Hi
    hinter deinen Formeln steckt eine gewisse Regelmäßigkeit.
    dh du kannst die benötigten Zellbezüge berechnen und daher reicht dir eine einzige Formel für den kompletten Zellbereich von H3 bis H444:
    WS.Range("H3:H444").FormulaLocal = "=INDEX('KW41'!A:R;GANZZAHL((ZEILE(A1)-1)/17)*37+7;REST(ZEILE(A1)-1;17)+2)"
    wenn das tatsächlich so ist, sollte dein Makro erheblich vereinfachen.
    Gruß Daniel
    Anzeige
    AW: ganz anderer Ansatz
    26.07.2019 19:28:45
    Michael
    Hallo Daniel
    Über diesen Ansatz muss ich erst mal Nachdenken, um zu verstehen was ich wo ändern muss, damit er auf alle Varianten passt. Meine Beispieldatei hatte auf allen 4 "Ü"Sheets die gleichen Formeln, der einfachheit wegen. Im Original hat natürlich jede "Ü" einen anderen Bezug.
    Ü001 bekommt die Daten aus den Zeilen KW41 B7-R7, B44-R44, B81-R81 usw. fortlaufend bis B932-R932.
    Ü002 bekommt die Daten aus den Zeilen KW41 B8-R8, B45-R45, B82-R82 usw. fortlaufend bis B933-R933.
    Also jedesmal alle Daten eine Reihe tiefer.
    Der Reiter KW42 sieht aus wie der 41er, dahingehend ist die Formel einfach abzuändern. An welchen Stellen definiere ich aber die Startzeile um die Formel für andere Ü's anzupassen. Und wie muss dann die fertige Sub aussehen? Ungefähr so?
    Sub x_ändern_NEU()
Dim ws As Worksheet
Dim arr
Dim z As Long
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then
WS.Range("H3:H444").FormulaLocal = _
"=INDEX('KW41'!A:R;GANZZAHL((ZEILE(A1)-1)/17)*37+7;  REST(ZEILE(A1)-1;17)+2)"
End If
Next
Application.ScreenUpdating = True
End Sub
    Viele Grüße
    Michael
    Anzeige
    AW: ganz anderer Ansatz
    26.07.2019 20:06:46
    Daniel
    Hi
    in meiner Formel ist die Startzeile die 7.
    den wert müsstest du dir dann entsprechend abändern.
    ggf kannst du ihm ja aus dem Tabellenblattnamen ableiten.
    Sub x_ändern_NEU()
Dim ws As Worksheet
Dim arr
Dim z As Long
dim FO as string
Dim Zeile as long
Application.ScreenUpdating = False
FO = "=INDEX('KW41'!A:R;GANZZAHL((ZEILE(A1)-1)/17)*37+ZZZ;  REST(ZEILE(A1)-1;17)+2)"
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then
Zeile = CLng(Mid(WS.Name, 2)) + 6
WS.Range("H3:H444").FormulaLocal = Replace(FO, "ZZZ", Zeile)
End If
Next
Application.ScreenUpdating = True
End Sub

    Gruß Daniel
    Anzeige
    AW: ganz anderer Ansatz
    27.07.2019 15:04:46
    Michael
    Hallo Daniel
    Sorry für die späte Rückmeldung, ich bin gestern nicht mehr dazu gekommen.
    Deine fertiger Code übersteigt mein Verständnis dafür, total. Es gibt darin viele Stellen die ich nicht Nachvollziehen kann, Z.B.:
    Dim z As Long - Was ist "z" und warum kommt z dann in Replace 3x vor. Es gibt ja keine Erklärung für z (z = ?)
    Aus den Namen der "Ü"Reiter lässt sich nichts ableiten, da sie nicht fortlaufend Nummeriert sind. Nach Ü018 kommt Ü390 gefolgt von Ü327. Das kann also nicht gehen.
    Ich habe eine große Bitte an dich. Kannst du mir zu deinem Code, Zeile für Zeile eine kurze Erklärung dazu schreiben. Vorallem auch, welche Stellen ich ändern muss, um eine Zeile Tiefer zu starten.
    Viele Grüße
    Michael
    AW: ganz anderer Ansatz
    27.07.2019 16:34:33
    Daniel
    Hi
    z kannst du vergessen.
    ich wollte ursprünglich mal z als Variable für die Zeilennummer nehmen, habe mich dann aber doch entschlossen, "Zeile" zu verwenden und dann vergessen, die Dimensionierung für z zu löschen.
    der Trick ist hier ein ganz einfacher:
    die Formel ist ja bis auf die Zeilennummer, welche bei jedem Blatt geändert werden muss, immer die gleiche.
    also habe ich das so gelöst, dass ich die Formel in einer Variablen ablege, und an der Stelle, an welcher die Zeilennummer eingefügt werden muss, einen Dummy-Text stehen habe, welcher ansonsten in der Formel nicht vorkommt. Diesen Dummy-Text tausche ich dann später durch die Zeilennummer aus.
    man könnte das natürlich auch so schreiben: 
    .FormulaLocal = "=INDEX('KW41'!A:R;GANZZAHL((ZEILE(A1)-1)/17)*37+" & Zeile & ";REST(ZEILE(A1)-1;17)+2)"

    aber ich finde das mit dem Replace übersichtlicher, vorallem wenn die Formeltexte länger werden und es mehrere einzufügende Variablen gibt.
    wenn du die Zeilennummer nicht aus dem Blattnamen berechnen kannst, dann musst man sich was anderes einfallen.
    Das einfachste wäre, die Zeilennummer bei jedem neuen Blatt um 1 zu erhöhen: 
    Sub x_ändern_NEU()
Dim ws As Worksheet
Dim Zeile as long
Zeile = 7
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Ü*" Then
WS.Range("H3:H444").FormulaLocal = "=INDEX('KW41'!A:R;GANZZAHL((ZEILE(A1)-1)/17)*37+"  _
_
& Zeile & "; REST(ZEILE(A1)-1;17)+2)"
Zeile = Zeile + 1
End If
Next
Application.ScreenUpdating = True
End Sub

    Gruß Daniel
    AW: ganz anderer Ansatz
    27.07.2019 18:13:54
    Michael
    Hallo Daniel
    Vielen Dank für deine Antwort. Das Meiste ist mir jetzt "fast" klar. Den Rest finde ich schon noch raus.
    Viele Grüße
    Michael

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige