Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1888to1892
1888to1892
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis in Werte umwandeln

Forumthread: Sverweis in Werte umwandeln

Sverweis in Werte umwandeln
18.07.2022 16:11:11
steven
Moin moin,
ich würde gern eine Kopie meiner Arbeitsdatei in Excel erstellen, in der die Sverweis Formeln automatisch durch Werte ersetzt werden und ansonsten alles gleich bleibt, damit ich die Datei auch an Menschen verschicken kann, die keinen Zugriff auf die Rohdaten haben. Meine Makro versuche sind bisher gescheitert.
Besten Dank im Voraus
Steven
Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
18.07.2022 16:20:10
neopa
Hallo Steven,
.. gibt es in der Datei auch andere Formeln außer den mit SVERWEIS() Müssen vorhandene Formate 1:1 erhalten bleiben. Wenn jeweils nein, gäbe es eine VBA-freie Alternative.
Gruß Werner
.. , - ...

AW: nachgefragt ...
20.07.2022 08:47:54
Steven
Hallo Werner,
es gibt auch andere Formeln wie zB ZÄHLENWENN etc. die ich dann auf gleichem Wege ersetzen würde. Das Layout/Format würde ich gern behalten. Ein anderer User hat für den Sverweis schon eine schöne Lösung geboten, vielen Dank für die Antwort!
Anzeige
AW: Sverweis in Werte umwandeln
18.07.2022 21:49:04
Rudi
Hallo,
teste mal

Sub KopieOhneFormelnSpeichern()
Dim wks As Worksheet
Application.ScreenUpdating = False
Worksheets.Select
ActiveWindow.SelectedSheets.Copy
For Each wks In ActiveWorkbook.Worksheets
With wks.Cells
.Copy
.PasteSpecial xlPasteValues
End With
Next wks
ActiveWorkbook.SaveAs _
ThisWorkbook.Path & _
"\Kopie - " & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5), xlOpenXMLWorkbook
End Sub
Gruß
Rudi
Anzeige
AW: Sverweis in Werte umwandeln
20.07.2022 08:50:16
Steven
Hi Rudi, danke für die schnelle Antwort. Wenn ich den Code so lese, bekomme ich wahrscheinlich das Problem, dass alle Formeln durch Werte ersetzt werden und es mir ein wenig das Format/layout der Datei zerschießt. In einer anderen Antwort hat Yal mir schon eine gute kleine Lösung geschickt, die ich wohl erstmal nutzen werde.
Anzeige
AW: Sverweis in Werte umwandeln
19.07.2022 16:16:46
Yal
Moin Steven,
probiere:

Sub SVerweisDurchWert_ersetzen()
Dim wks As Worksheet
Dim z As Range
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
For Each z In wks.Cells.SpecialCells(xlCellTypeFormulas)
If Left(z.FormulaLocal, 10) = "=SVERWEIS(" Then z.Value = z.Value
Next
Next
Application.ScreenUpdating = True
End Sub
Kopie hat vorher stattgefunden, speichern muss noch anschliessend erfolgen.
VG
Yal
Anzeige
AW: Sverweis in Werte umwandeln
20.07.2022 08:45:42
Steven
Moin Yal, vielen Dank, das hilft mir auf jeden Fall sehr weiter.
Ich denke mal für andere Formeln wie zB. ZÄHLENWENN geht es genauso, wenn man den "=SVERWEIS" in der If schleife entfernt?
Schöne warme Woche noch
AW: Sverweis in Werte umwandeln
20.07.2022 09:33:28
Steven
Edit: Wenn ich das mit ZÄHLENWENN mache, passiert nix, liegt das am Ä?
AW: Sverweis in Werte umwandeln
20.07.2022 09:49:32
Yal
Hallo Steven,

If Left(z.FormulaLocal, 10) = "=SVERWEIS(" Then z.Value = z.Value
Left (x, 10) bedeutet, es wird nur die erste 10 linke Stelle des Text x beachtet.
"=ZÄHLENWENN(" ist 12 Buchstaben lang,
"=ZÄHLENWEN" wäre 10 Buchstaben lang.
Versucht den Code Dir selbst laut und auf Deutsch zu lesen: wenn die 10 linke Stellen der Formel gleich wie ... sind, dann...
Man kommt dann auf solchen Stolperfall sehr schnell drauf.
VG
Yal
Anzeige
AW: Sverweis in Werte umwandeln
20.07.2022 11:43:56
Daniel
Hi
wäre es nicht sinnvoller, grundsätzlich alle Formeln in Werte zu wandeln, die die Rohdaten verwenden?
das könntest du einfach per Mausklick über den Menüpunkt DATEN - VERBINDUNGEN - VERKNÜPFUNGEN BERABEITEN - VERKNÜPFUNGEN LÖSCHEN für die ganze Mappe tun.
ansonsten kannst du dir den Code von YAL auch so umschreiben, dass er alle Formeln, die die Datei "Rohdaten.xls" verwenden, in Werte wandelt, das geht mit diesem Code:

If z.FormulaLocal Like "*Rohdaten.xlsx*" Then z.Value = z.Value
Gruß Daniel
Anzeige
AW: Sverweis in Werte umwandeln
20.07.2022 12:48:11
Steven
Moin Daniel, Spezifische Verknüpfungen per Mausklick löschen ist auch auf jeden Fall ein gute Lösung, danke dir!
;

Forumthreads zu verwandten Themen

Hintergrundfarbe in Zahl umwandeln
Timo

Hallo zusammen.
Gibt es über VBA eine Möglichkeit, die Hintergrundfarbe einer Zelle in eine Zahl umzuwandeln und das schneller als mit for- Schleifen?
Habe einen Bereich ("J16:AG39"), wo ich per Zellaktivierung mit der Maus Zellen oder auch Zellbereiche mit einer Hintergrundfarbe verseh...
07.08.2008 19:30:00
Thread-ID: 1000082
4 Beiträge
Anzeige
Formel "Text" ROTE Zahl bei Minuswert?
dieter(drummer)

Hi Formelspezialisten,
in Zelle B2 steht dise Formel (funktionert auc h):
=TEXT(I4-D4;"00.0")&" ("&TEXT(I4/D4-1;"0%")&")".
Mein Ziel, dass eine Minuszahl in ROT erscheint. Hab's mit bedingte Formatierung versucht und bekomme es nicht hin.
Gibt es eine Möglichkeit, a...
12.08.2008 10:59:29
Thread-ID: 1001135
7 Beiträge
Letzter Wert einer Spalte (Matrixformel)
{mskro}

Hallo Excelaner,
ich benötige mal wieder eine Hilfestellung.
Es soll der Wert in einer Spalte angezeigt werde, der in der untersten Zeile steht.
Mittels der Recherche habe ich nun folgende MatrixFormel erstellt:
=INDEX(Z1:Z5000;MAX(WENN(Z1:Z5000"";ZEILE(1:5000)));1)
Nur wird...
28.08.2008 09:59:21
Thread-ID: 1005303
9 Beiträge
Anzeige
von Datum bis Datum suchen und Werte in die Spalte
Datum

Hallo liebe Excel-Könner,
ich habe folgendes Problem und finde keine Lösung.
Ich habe in Spalte A von 2006 bis heute Datum stehen, über eine UserForm möche ich in zwei TextBoxen von bis Datum eintragen und in Spalte B in diesem Bereich den WertX.
Ich hoffe Ihr könnt mir helfen!
...
07.09.2008 09:31:20
Thread-ID: 1007931
8 Beiträge
Verschiedene Werte in bestimmte Zahlen umwandeln
Henning

Hallo, ich versuche grade bei einem Schichtplan die Verschiedenen Zelleninhalte z.B. Zelle A3 Inhalt "U1",Zelle A4 Inhalt"1/3" usw. in Werte umzuwandeln und diese dann als Stunden ausrechennen zu lassen.
Also so soll z.B. aus dem Inhalt von Zelle A3, dort steht "U1" soll dann 8 Stunden ents...
15.10.2008 18:37:00
Thread-ID: 1016648
4 Beiträge
Array-Formel in VBA-Code umwandeln?
Robert

Hallo,
ich bräuchte bitte eure Hilfe.
Hab folgende Formel...
Selection.FormulaArray = _
=IF(ROW(R[-107]C[-3])>COUNTA('1'!R5C[4]:R200C[4]),"""",INDEX('1'!R5C4:R200C4,SMALL(IF('1'!R5C[4]:R200C[4]<>"""",ROW(R1:R196),""""),ROW(R[-107]C[-3]))))"
Mein Problem ist, dass ic...
26.10.2008 10:56:28
Thread-ID: 1019326
13 Beiträge
Anzeige
In Zahlen umwandeln
fuxl

Ich hab ein AutoOpen Makro in dem ich die Funktion TextinSpalten verwende um den Zelleninhalt in eine Zahl umzuwandeln und die kleinen Dreiecke los zu werden. Das ganze geschieht mit folgender Zeile:
'Tabelle1.Range("H15:H$" & letzteZeile).TextToColumns Destination:=Tabelle1.Range("H15"...
19.11.2008 11:13:06
Thread-ID: 1025937
5 Beiträge
Anzeige
Text in Zahlen umwandeln
u_hoernchen

Ich habe eine Spalte in Excel, in der vereinzelt Zellen sind, in denen Zahlen als Text formatiert sind. Da ich diese Zahlen für die weitere Verwendung als Werte haben muss und ich das dann relativ oft umfrickeln muss (ist leider ein Download aus einem Vorprogramm), frage ich mich, ob es ggf. ei...
26.11.2008 11:09:00
Thread-ID: 1027963
4 Beiträge
Datum in Zahl umwandeln
P.Mackes

Hallo,
was ich machen möchte ist folgendes:
ich habe eine Zahl, die ein Datum darstellt, z.B. 20080507. Dabei handelt es sich NICHT um ein Datumsformat, sondern um eine richtige Zahl. Jetzt muss ich davon allerdings 90 Tage abziehen. Also wandle ich mit Date(left...mid...right) das gan...
27.11.2008 00:37:57
Thread-ID: 1028186
3 Beiträge
Formel in Makro umwandeln?
Josef_T

Guten Abend zusammen.
Habe mal wieder ein Problem, dass ich alleine nicht gelöst bekomme.
Es geht darum, einen Ausdruck zu machen wobei nur Zeilen gedruckt werden sollen, die in C einen Wert haben, der grösser Null ist.
Habe mal ein Muster hochgeladen, weil meine Fragen und Erklärun...
29.11.2008 19:08:00
Thread-ID: 1028896
6 Beiträge
Formel einfügen wenn in A:A Werte stehen
Micha

Hallo zusammen,
ich bastle mal wieder an meinen Münzdateien und komme nicht so richtig weiter.
Mein Problem:
In Spalte A stehen Werte (z.B. 5 Pf), diese möchte ich nun trennen [in B2 soll per VBA die Funktion =Links(A2;1) / FormulaR1C1 = "=LEFT(RC[-53],1) ].
Diese Formel sollte...
14.01.2009 21:28:00
Thread-ID: 1039822
13 Beiträge
ein Format in Zahl umwandeln VBA
Michael

Hallo!
Welche Möglichkeit gibt es über VBA eine Tabelle die ich zugesendet bekomme. Die Werte in Zahlen umzuwandeln. Beispiel
number name___ old__ new Change Change %
500041 system 23.26 23.28 -0.71 -2.45% #WERT!
540014 cpu 69.95 69.99 -0.22 -0.31% #WERT! =F...
29.01.2009 10:37:00
Thread-ID: 1044870
7 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Sverweis in Werte umwandeln: Praktische Anleitung

Schritt-für-Schritt-Anleitung

Um SVERWEIS-Formeln in Werte umzuwandeln, kannst du die folgenden Schritte befolgen:

  1. Öffne Deine Excel-Datei, in der die SVERWEIS-Formeln enthalten sind.

  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.

  3. Füge ein neues Modul hinzu: Rechtsklicke im Projektfenster auf "VBAProject (deineDatei.xlsm)" und wähle "Einfügen" > "Modul".

  4. Kopiere den folgenden Code in das Modul:

    Sub SVerweisInWerteUmwandeln()
   Dim ws As Worksheet
   Dim z As Range
   Application.ScreenUpdating = False

   For Each ws In ActiveWorkbook.Worksheets
       For Each z In ws.Cells.SpecialCells(xlCellTypeFormulas)
           If Left(z.FormulaLocal, 10) = "=SVERWEIS(" Then
               z.Value = z.Value
           End If
       Next z
   Next ws

   Application.ScreenUpdating = True
End Sub

  5. Schließe den VBA-Editor und kehre zu Excel zurück.

  6. Führe das Makro aus: Gehe zu Entwicklertools > Makros, wähle SVerweisInWerteUmwandeln aus und klicke auf Ausführen.

Mit dieser Methode werden alle SVERWEIS-Formeln in Werte umgewandelt, während das Layout deiner Datei erhalten bleibt.

Häufige Fehler und Lösungen

  • Fehler: SVERWEIS gibt #WERT! zurück

    • Lösung: Überprüfe die Eingabewerte und den Suchbereich. Wenn der SVERWEIS nicht das erwartete Ergebnis liefert, kann das an falschen Daten liegen.

  • Problem: Das Layout wird zerschossen

    • Lösung: Achte darauf, nur die SVERWEIS-Formeln in Werte umzuwandeln und andere Formeln unberührt zu lassen. Nutze den oben genannten VBA-Code, um gezielt nur die SVERWEIS-Formeln zu ersetzen.

  • Fehler: ZÄHLENWENN funktioniert nicht

    • Lösung: Ändere die Bedingung im Code von SVERWEIS zu ZÄHLENWENN, um auch diese Formeln in Werte umzuwandeln.

Alternative Methoden

  1. Manuelle Umwandlung:

    • Markiere die Zellen mit SVERWEIS-Formeln.
    • Kopiere sie (CTRL + C).
    • Rechtsklick auf die Zielzelle und wähle Inhalte einfügen > Werte.

  2. Excel-Funktion nutzen:

    • Wähle die Zellen mit Formeln und drücke STRG + C, dann STRG + ALT + V, gefolgt von V, um die Formeln in Werte umzuwandeln.

  3. Verknüpfungen in Werte umwandeln:

    • Gehe zu Daten > Verbindungen > Verknüpfungen bearbeiten und lösche die Verknüpfungen, um alle Formeln, die auf externe Daten zugreifen, in Werte umzuwandeln.

Praktische Beispiele

  • Beispiel 1: Angenommen, du hast eine Tabelle mit SVERWEIS-Formeln, die auf eine andere Tabelle referenzieren. Nach dem Ausführen des Makros sind die SVERWEIS-Ergebnisse nun feste Werte, die verschickt werden können.

  • Beispiel 2: Wenn du ein Ergebnis einer SVERWEIS-Formel in Text umwandeln möchtest, kannst du =TEXT(SVERWEIS(...), "Format") verwenden, um das Format anzupassen.

Tipps für Profis

  • Verwende immer ein Backup deiner Excel-Datei, bevor du Makros oder Massenänderungen vornimmst.
  • Testen: Probiere den VBA-Code zuerst in einer Testdatei aus, um sicherzustellen, dass alles funktioniert, wie gewünscht.
  • Anpassungen: Du kannst den VBA-Code leicht anpassen, um andere Formeln wie ZÄHLENWENN ebenfalls in Werte umzuwandeln, indem du die Bedingung entsprechend änderst.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Formeln gleichzeitig in Werte umwandeln? Du kannst den VBA-Code anpassen, um auch andere Formeln wie ZÄHLENWENN oder WVERWEIS in Werte umzuwandeln, indem du die entsprechenden Bedingungen hinzufügst.

2. Was passiert, wenn ich die Datei ohne vorherige Umwandlung verschicke? Wenn du die Datei ohne Umwandlung verschickst, können Empfänger ohne Zugriff auf die Rohdaten die Formeln nicht ausführen, was zu #WERT! oder leeren Zellen führen kann. Es ist daher ratsam, die Formeln in Werte umzuwandeln, bevor du die Datei teilst.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige