Findmethode in VBA soll Sverweise ersetzen

Question

Hi,
irgendwie stehe ich auf dem Schlauch. Ich krieg die FIND() Methode einfach nicht in mein Hirn.
Folgende Aufgabenstellung:
In Tabelle 1 werden per Sverweis Vorschauwerte (Spalte B) aus einer Tabelle 2 gefüllt. Soweit so einfach.

Nun möchte ich die Sverweis-Formeln per Makro mit Werten ersetzen. Hierzu soll sich die Findmethode als Schleife am besten eignen. Hier jedoch fehlt mir irgendwie der Zugang.

Weitherhin ist in der zu befüllenden Spalte B auch andere Formeln (außer Sverweis) in einzelnen Zellen die auf keinen Fall überschrieben werden dürfen.

Ich bräuchte also zusätzlich die Abfrage: Steht da eine Formel drin, dann überschreibe nicht.

Kann mir jemand einen kleinen Denkanstoß geben?
Vielen Dank
Mosmu

Answer 1

Hi,

wenn nur die bereits vorhandenen SVERWEIS()-Ergebnisse in einen Wert umgewandelt werden sollen, wozu dann per Find-Methode? Das geht doch auch in einer Schleife über alle Zellen mit Formeln, indem du prüfst ob die Formel "SVERWEIS" enthält:

Sub WerteEintragen()
Dim rngZelle As Range
For Each rngZelle In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If InStr(rngZelle.FormulaLocal, "SVERWEIS") > 0 Then rngZelle.Value = rngZelle.Value
Next rngZelle
End Sub

Bis später,
Karin

Answer 2

Hallo Karin,
sorry für die späte Rückmeldung. Herzlichen Dank für deinen Beitrag.
Nur leider habe ich mich wohl etwas falsch ausgedrückt.
Ich möchte nicht einzelne Zellen mit Sverweis Formeln ersetzen sondern die METHODE SVERWEIS ersetzen.

Aktueller Stand:
Tabelle A greift per Sverweis auf eine Tabelle B zu.
In der Spalte mit den Sverweis Formeln sind auch in einzelnen Zeilen andere Formeln enthalten.

Mein Ziel Sverweis verbannen :
Die Informationen aus Tabelle B sollen also per Makro (Findmethode?) in die Tabelle A geschrieben werden OHNE jedoch die Zellen zu zerstören in denen andere Formeln stehen.

Weist du was ich meine?
Vg
jörg

Answer 3

Hi Jörg,

mal eine generelle Frage: weshalb willst du so etwas machen? SVERWEIS() ist doch eine wunderbare Funktion und weshalb muss man alles per Makro lösen wollen, wenn es auch per Formel geht?!

Hier ein prinzipieller Code:

Sub Finden()
Dim rngZelle As Range
Dim rngSuche As Range
For Each rngZelle In ActiveSheet.Range("B1:B10")
If rngZelle.Offset(0, 3).HasFormula Then
If InStr(rngZelle.Offset(0, 3).FormulaLocal, "SVERWEIS") > 0 Then
With Worksheets("Tabelle2").Columns(1)
Set rngSuche = .Find(rngZelle, lookat:=xlWhole)
If Not rngSuche Is Nothing Then rngZelle.Offset(0, 3) = .Cells(rngSuche, 1).Offset(0, 2)
End With
End If
End If
Next rngZelle
Set rngZelle = Nothing
Set rngSuche = Nothing
End Sub

Durchlaufen wird B1:B10, die Formeln stehen in E1:E10, gesucht wird in Tabelle2!Spalte A und aus Spalte C der Tabelle2 wird der zugehörige Wert übernommen.

Bis später,
Karin

Bis später,
Karin

Answer 4

Hi Karin,
vielen Dank schon mal vorab. Meine Güte bist du schnell!

Beim ersten drüberschauen ist es dass jedoch immer noch nicht ganz. Ich habe keine Sverweisformeln mehr. Es gibt nur noch WERTE und Formeln (kein Sverweis) in der Zielspalte. Das Makro soll nun die WERTE mit neuen Werten ersetzen (aus der Quelltabelle) ohne dabei die Formeln zu zerschießen. Da sich die Struktur ändert kann ich nicht einfach copy und paste machen...

Ich schau trotzdem mal ob ich deinen Code grundsätzlich verwenden kann. Zum reinfuchsen eine super Grundlage, vielen Dank!

Warum kein Sverweis?
Diese Tabelle (Master) zeigt Vorschauwerte die aus dieversen Bereichen zusammengetragen werden. Wenn ich nun Vorschauwerte "händisch" kurz ändern möchte muss ich dies in der Ursprungsdatei machen und nicht in meiner Masterdatei da mir ja sonst die Formeln zerschossen werden.
Da die Werte aus den Bereichen aber sowieso nur einmal im Monat angepasst werden reicht eine einfache Importfunktion für mich aus, das soll das Makro lösen.

Nebenbei erhöht dies die Geschwindigkeit enorm.

vg jörg

Answer 5

Hi Jörg,

wenn die Ergebnisse der SVERWEIS()-Formeln nun durch ihre Werte ersetzt sind, musst du den Code nur ein wenig anpassen (ungetestet):

Sub Finden()
Dim rngZelle As Range
Dim rngSuche As Range
For Each rngZelle In ActiveSheet.Range("B1:B10")
If Not rngZelle.Offset(0, 3).HasFormula And rngZelle <> "" Then
With Worksheets("Tabelle2").Columns(1)
Set rngSuche = .Find(rngZelle, lookat:=xlWhole)
If Not rngSuche Is Nothing Then rngZelle.Offset(0, 3) = .Cells(rngSuche, 1).Offset(0, 2)
End With
End If
Next rngZelle
Set rngSuche = Nothing
Set rngZelle = Nothing
End Sub

Bis später,
Karin

Answer 6

Hallo Karin,
vielen Dank! Ich probiere es später aus und melde mich.
vg
jörg

Answer 7

Hi Karin,
es wird erkannt ob Formel oder nicht, aber leider werden die Werte nur gelöscht. Die Werte aus Tabelle 2/Spalte C werden nicht übernommen.

vg jörg

Answer 8

Hi Jörg,

da ist noch ein Fehler im Code - ändere diesen Teil: .Cells(rngSuche, 1).Offset(0, 2) in diesen .Cells(rngSuche.Row, 3), wenn die Werte aus Spalte 3 (C) übernommen werden sollen.

Bis später,
Karin

Answer 9

SENSATIONELL! Es funktioniert, vielen Dank.

Jetzt habe ich nur noch eine Frage, wie kann ich Alphanumerische Werte (als Bezug, nicht die zu erstetzende Zahl) finden. Aktuell geht es nur mit Zahlen.

vg jörg

Answer 10

ES FUNKTIONIERT.
JETZT NIMMT ER AUCH ALPHANUMERISCHE WERTE.
VIELEN VIELEN DANK!