Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 09:26:26
Median für Zeilen mit gleichen Zeitstempel
19.04.2024 08:41:23
Zwei Makros in ein Arbeitsblatt - zweite wird nicht ausführt
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
524to528
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
524to528
524to528
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formeln beim Blattwechsel durch Werte ersetzen

Formeln beim Blattwechsel durch Werte ersetzen
29.11.2004 12:37:59
Fritz
Hallo VBA-Spezialisten,
ich bin wieder einmal auf eure Hilfe angewiesen:
In der Tabelle1 befinden sich in den Bereichen D4:AK16 sowie D20:AK32 Formeln, die sicherstellen, dass - unter bestimmten Voraussetzungen in die jeweilige Zelle Werte(und zwar jeweils Zahlen) - übernommen werden. Ich möchte nun mittels VBA erreichen, dass beim Verlassen dieser Tabelle die Formeln in allen Zellen dieser Bereiche, die zu diesem Zeitpunkt eine Zahl enthalten durch die jeweilige Zahl (alos den aktuellen Wert der Zelle) ersetzt werden. Die Zellen in diesen Bereichen, die keine Zahlen enthalten, dürfen nicht verändert werden, d.h. die Formel darf nicht gelöscht werden.
Ich hoffe, dass die Aufgabenstellung für euch nachvollziehbar ist und bedanke mich bereits an dieser Stelle für jede Form von Hilfe
Mfg
Fritz
AW: Formeln beim Blattwechsel durch Werte ersetzen
Udo
Gehe zu Formeln/Zahlen, kopieren, Inhalte einfügen Werte. Das kannst du auch aufzeichnen, falls es VBA sein soll.
Udo
AW: Formeln beim Blattwechsel durch Werte ersetzen
Fritz
Hallo Udo,
wie kann ich nun erreichen, dass das aufgezeichnete Makro jeweils automatisch beim Verlassen der Tabelle1 ausgeführt wird?
Gruß
Fritz
AW: Formeln beim Blattwechsel durch Werte ersetzen
Udo
Dafür gibts das Ereignis Private Sub Worksheet_Deactivate()
Udo
AW: Formeln beim Blattwechsel durch Werte ersetzen
Dr.
Die Routine in ein "Private Sub Worksheet_Deactivate()" in das entprechende Tabellenblatt einbauen.
AW: Formeln beim Blattwechsel durch Werte ersetzen
Fritz
Hallo Udo,
ich habe gerade festgestellt, dass die von dir vorgeschlagene Lösung nicht den gewünschten Erfolg bringt. Kopiere ich jeweils den gesamten Bereich, werden anschließend in den Zellen, die keine Werte, jedoch die Formeln enthalten, diese gelöscht. Markieren der Zellen mit den Werten kann ich über Makroaufzeichnung nicht erreichen, da jeweils unterschiedliche Zellen Werte enthalten.
Ich benötige also eine Lösung über einen speziellen VBA-Code.
Mfg
Fritz
Anzeige
AW: Formeln beim Blattwechsel durch Werte ersetzen
Udo
Dann muss ich passen.
Udo
VBA-Spezialisten - Wer kann helfen?
Fritz
Hallo VBA-Spezialisten,
setze nun meine Hoffnung auf eure Fähigkeiten, von denen ich in diesem Forum schon des Öfteren profitieren durfte.
Mfg
Fritz
AW: VBA-Spezialisten - Wer kann helfen?
Ingolf
Hallo Fritz,
versuchs mal damit:

Sub Worksheet_Deactivate()
Dim rngZelle As Range
Application.EnableEvents = False
For Each rngZelle In Me.UsedRange
If rngZelle > 0 Then
rngZelle.Copy
rngZelle.PasteSpecial (xlPasteValues)
End If
Next rngZelle
Application.EnableEvents = True
End Sub

Gruß Ingolf
AW: VBA-Spezialisten - Wer kann helfen?
Fritz
Hallo Ingolf,
funktioniert leider nicht. Zellen mit Formeln werden auch hier gelöscht.
Dennoch vielen Dank für Deine Mühen.
Gruß
Fritz
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
yps
hi,
probier mal:
For Each zelle In Union(Range("D4:AK16"), Range("D20:AK32"))
If zelle.Value "" Then zelle.Value = zelle.Value
Next
cu Micha
AW: VBA-Spezialisten - Wer kann helfen?
Ingolf
Hallo Fritz,
hast du deine Formeln vielleicht so geschrieben, dass solange noch Einträge für die Berechnung der Formel fehlen, ein Leerstring ("") in die betreffende Zelle geschrieben wird, also nach dem Muster =wenn(A1="";"";A1)? In diesem Fall mußt du in dem Makro die Zeile
If rngZelle &gt 0 then
durch
If rngZelle &lt&gt "" then
ersetzen.
Gruß Ingolf
AW: VBA-Spezialisten - Wer kann helfen?
Fritz
Hallo Ingolf, hallo Micha
ihr müßt bitte entschuldigen, mir wird es langsam peinlich aber es klappt nicht.
Mir fehlen offensichtlich selbst die VBA Kenntnisse um mit euren gut gemeinten Vorschlägen klar zu kommen.
An Ingolf: Ich habe das inzwischen geändert. Auswirkung: Beim Verlassen des Tabellenblatts wird nun überhaupt nichts mehr verändert. Aber wie gesagt: Es liegt vermutlich daran, dass ich etwas falsch mache, nur weiß ich nicht was.
An Micha: Aufgrund meiner nicht einmal einem Anfänger entsprechenden VBA-Kenntnissen konnte ich Deinen Vorschlag nicht umsetzen. Bitte um Verständnis. Vielleicht kannst Du mir die "Vorgehensweise" etwas ausführlicher beschreiben.
An dieser Stelle danke an euch beide (auch für eure Geduld)
Gruß
Fritz
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
Ingolf
Hallo Fritz,
lad das Ding doch mal hoch. Entscheidend ist, was in den betreffenden Zellen drinsteht, solange die Formel stehen bleiben soll und was drin steht, wenn die Formel durch Werte ersetzt werden soll.
Gruß Ingolf
AW: VBA-Spezialisten - Wer kann helfen?
Josef
Hallo Fritz!
Das sollte es tun!

Private Sub Worksheet_Deactivate()
Dim rng As Range
On Error Resume Next
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each rng In Me.Cells.SpecialCells(xlCellTypeFormulas, 1)
rng = rng.Value
Next
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Rechtsklick auf Blattregister &gt Code anzeigen und in das Rechte Fenster einfügen!
Gruß Sepp
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
Fritz
Hallo Ingolf, Hallo Sepp
ich war mal kurz weg, deshalb melde ich mich erst jetzt.
Sepp hat mir in der Vergangenheit schon einige Male bei schwierigen Problemen geholfen. Da es auch bei seinem Code nicht funktioniert, habe ich bei der Problemstellung wohl etwas entscheidendes nicht angegeben. Ich lade euch also mal die Datei hoch.
Nochmals vielen Dank für eure Hilfe, auch für eure Geduld, die ihr mit uns Anfängern immer wieder aufbringen müsst. Einfach vorbildlich!
Gruß
Fritz
https://www.herber.de/bbs/user/14088.xls
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
Ingolf
Hallo Fritz,
ich hab dir das Makro mal in die Datei reingestellt. Bei mir funktioniert das so. Sobald du die Tabelle1 verlässt, werden die entsprechenden Formeln in Werte umgewandelt.
https://www.herber.de/bbs/user/14090.xls
Gruß Ingolf
AW: VBA-Spezialisten - Wer kann helfen?
Fritz
Hallo Ingolf,
vielen Dank jetzt funktioniert die Sache.
Allerdings wollte ich eigentlich nur die Zellen im angegebenen Bereich - in der Tabelle die hellgrün unterlegten Zellen - überprüfen. Bei der vorliegenden Lösung werden auch andere Zellen im Tabellenblatt, die Werte enthalten "verändert".
Wenn man das vielleicht noch ändern könnte?
Ich befürchte aber, dass eine diesbezügliche Abänderung des Codes mit erheblichem Aufwand verbunden ist und ich will dir das eigentlich nicht mehr zumuten.
Ich beneide euch VBA-Kenner um eure Kenntnisse und hoffe, dass ich irgendwann einmal Zeit finde mich etwas näher mit diesem Thema beschäftigen zu können.
Nochmals danke für die viele Arbeit.
Gruß
Fritz
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
Ingolf
Hallo Fritz,
danke für das Kompliment. Rückmeldungen, zumal solche, sind ja immer gern gesehen. Wenn du im Makro den Begriff "UsedRange" durch "Range("$D$4:$AK$32")" ersetzt, prüft er die beiden grünen Bereiche und die dazwischenliegenden weissen Zellen. Wenn du diese Zwischenzellen auch aussparen mußt, dann ersetze im Makro den Bereich von "For each..." bis "Next rngZelle" durch folgenden Code:
For Each rngZelle In Me.Range("$D$4:$AK$16")
If rngZelle "" Then
rngZelle.Copy
rngZelle.PasteSpecial (xlPasteValues)
End If
Next rngZelle

For Each rngZelle In Me.Range("$D$20:$AK$32")
If rngZelle "" Then
rngZelle.Copy
rngZelle.PasteSpecial (xlPasteValues)
End If
Next rngZelle
Gruß Ingolf
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
y
hi Fritz,
zu dritt klappt das schon *grins* (die helfer sind ja in der überzahl)
in anlehnung an den code von Sepp :

Private Sub Worksheet_Deactivate()
Dim rng As Range
On Error Resume Next
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each rng In Union(Range("D4:AK16"), Range("D20:AK32")).SpecialCells(xlCellTypeFormulas, 1)
rng = rng.Value
Next
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

cu Micha
Anzeige
AW: VBA-Spezialisten - Wer kann helfen?
Fritz
Hallo Micha,
vielen Dank, werds gleich probieren. Ich gehe davon aus, dass jetzt alle Wünsche realisiert wurden.
Schöne Grüße an Dich und die beiden anderen Helfer!
Fritz

304 Forumthreads zu ähnlichen Themen

Formel automatisch durch Wert ersetzen (vba)
Tanja

Hallo liebe Leute,
Ich hab in meiner excel-Mappe, in den Zellen: M2:M14 Formeln, die eine ganze Zahl als Ergebnis erzeugen.
So, und nun möchte ich das 1. Berechnungs-Ergebnis quasi "einfrieren".
Mein Problem: Ich möchte kein Makro per Hand auslösen müssen, sondern der Code soll voll...
07.08.2008 21:59:00
Thread-ID: 1000110
5 Beiträge
Anzeige
Verknüpfungen durch Werte ersetzen
Horst

Hallo Excel-Freunde!
Wie kann ich bei folgendem Makro angeben, dass nur Verknüpfungen ab der Spalte AA (Spalte 30) durch Inhalte ersetzt werden. Alle anderen Verknüpfungen sollen erhalten bleiben.
Sub update() Dim i As Long For i = 1515 To ActiveSheet.Cells(Rows.Count, 30).End(xlUp).R...
22.02.2009 12:18:26
Thread-ID: 1052805
4 Beiträge
Bei Blattschutz- Formeln durch Werte ersetzen
Mandy

Hallo Ihr Lieben,
habe mal wieder eine Frage: ich lade mit folgenden Code eine Datei und kopiere alle Tebellenblätter aus der Quelle bei mir rein.
Die Tabellenblätter der Quelle, die da eingelesen werden, habe zum Teil untereinander Verknüpfungen (Verweise). Beim einlesen wollte ich abe...
05.05.2009 15:04:37
Thread-ID: 1073102
7 Beiträge
Anzeige
Bestimmte Formeln in durch Werte ersetzen
Steffen

Hallo zusammen,
ich habe folgendes Problem:
ein spezielle Formel (Hyperion Retrieve AddOn(HPVAL)) transportiert externe Daten aus meinem Konsolidierungsprogramm in verschiedene Tabellenblätter einer Exceldatei. Diese externen Daten werden über normale Excelformeln in weiteren Tabellenbl...
21.08.2009 20:57:43
Thread-ID: 1097849
2 Beiträge
Wie Zahlenwerte durch Absolutwerte ersetzen?
Hans

Hallo,
wie kann ich in einer Spalte, die positive und negative Werte enthält, alle Werte durch deren Absolutwert ersetzen. Das ganze muß ich pro Spalte nur einmal machen, es wäre also ausreichend und von Vorteil, wenn das irgendwie mit dem Suchen/Ersetzen Dialog hinzubekommen wäre (ohne Mak...
02.03.2010 14:31:21
Thread-ID: 1142729
3 Beiträge
Wie Zahlenwerte durch Absolutwerte ersetzen?
Hans

Hallo,
wie kann ich in einer Spalte, die positive und negative Werte enthält, alle Werte durch deren Absolutwert ersetzen. Das ganze muß ich pro Spalte nur einmal machen, es wäre also ausreichend und von Vorteil, wenn das irgendwie mit dem Suchen/Ersetzen Dialog hinzubekommen wäre (ohne Mak...
02.03.2010 15:13:40
Thread-ID: 1142750
4 Beiträge
SVERWEISE finden und durch Werte ersetzen
grexx123

HAllo,
wie schon im Betreff beschrieben suche ich ein VBA-Schnipsel, das
1. Alle Sverweise sucht
2. Zellen, in denen ein SVERWEIS steht, mit "Wert" füttern (also kopieren und als Wert einfügen)
Ich weiß, dass man Verknüpfungen lösen kann, jedoch habe ich sowohl die Formel als au...
08.03.2010 13:25:36
Thread-ID: 1144304
6 Beiträge
Formeln in Zellen durch VBA ersetzen
Kuhlmey

Hallo, liebe Excel-Freunde ich habe folgendes Problem:
Mittels VBA setzte ich durch Doppelclick ein „X“ in bestimmte Zellen. Diese Zellen werden
ausgelesen und für nachfolgende Formel benötigt:
{=WENN(ZEILE()>ZÄHLENWENN(Tabelle1!A:A;"WAHR");"";INDEX(Tabelle1!B:B; KKLEINSTE(WENN(...
13.03.2010 14:03:05
Thread-ID: 1145598
18 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
17
3
6
4
1
2
3
5
17
2
6
10
3
4
7
8
10
9
2
1
5
13
2
3