Excel 2013 VBA: FindReplace

[sharky51]

Hallo zusammen,

sitze wieder mal an einer für mich kniffligen Aufgabe und hoffe auf Eure Hilfe.

Ich habe eine Tabelle in der in Spalte 4 Abkürzungen stehen. Diese möchte ich nun in Spalte 6 übertragen bzw. den Langtext zu diesen Abkürzungen dort eintragen.
Die Tabelle hat auch eine Überschrift-Zeile, die natürlich nicht verändert werden sollte.
Aus dem Netz habe ich auch ein Beispiel gefunden...siehe unten...das schon mal in die Richtung geht, aber alle gefundenen Werte ersetzt.

Wie müsste der Code geändert werden damit nur die Spalte 4 ausgelesen wird und das "Langwort in die Spalte 6 eingetragen wird...und das nur begrenzt in der aktuellen Tabelle?

Code:

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList= Array("C", "U", "M")
rplcList = Array("Canada", "United States", "Mexico")

'Loop through each item in Array lists
 For x = LBound(fndList) To UBound(fndList)
   'Loop through each worksheet in ActiveWorkbook
     For Each sht In ActiveWorkbook.Worksheets
       sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
         LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
         SearchFormat:=False, ReplaceFormat:=False
     Next sht
 Next x
End Sub

Vielen Dank für Eure Hilfe!

[GMG-CC]

Moin,
gibt es einen Grund, dass du auf SVERWEIS() verzichtest? Ich bin zwar VBA-Fan, aber nur da, wo es mir sinnvoll erscheint.

[sharky51]

Moin,
gibt es einen Grund, dass du auf SVERWEIS() verzichtest? Ich bin zwar VBA-Fan, aber nur da, wo es mir sinnvoll erscheint.

Moin,
würde das lieber mit VBA machen und keine Formeln ins Blatt schreiben.
Trotzdem danke für den Hinweis.

[Kuwer]

Hallo Erich,

ersetze Cells durch Columns(4):

     sht.Columns(4).Replace What:=fndList(x), Replacement:=rplcList(x), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False

Nachtrag: Nur für das aktuelle Sheet dann so:

'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveSheet
ActiveSheet.Columns(4).Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next x

Gruß Uwe

[sharky51]

Hallo Erich,

ersetze Cells durch Columns(4):

     sht.Columns(4).Replace What:=fndList(x), Replacement:=rplcList(x), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False

Nachtrag: Nur für das aktuelle Sheet dann so:

'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
  'Loop through each worksheet in ActiveSheet
   ActiveSheet.Columns(4).Replace What:=fndList(x), Replacement:=rplcList(x), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
     SearchFormat:=False, ReplaceFormat:=False
Next x

Gruß Uwe

Hallo Uwe,

vielen Dank funktioniert schon recht gut. Ich möchte aber das "Langwort" in Spalte 6 haben. Spalte 4 mit dem Kürzel sollte unberührt bleiben.
Und noch etwas ist mir aufgefallen...wenn ein Kürzel (von denen habe ich viele) aus mehreren Zeichen besteht dann funktioniert das nicht, bzw. der Code tut was er tun soll...er sucht nach allen Kriterien.
wenn z.B. anstatt:
fndList= Array("C", "U", "M")
rplcList = Array("Canada", "United States", "Mexico")

das hier vorgegeben wird:
fndList= Array("C", "UC", "M")
rplcList = Array("Canada", "United States", "Mexico")
..dann erhalte ich bei "UC" nicht "United States"...sondern "United StatesC"

Gibt es da eine Möglichkeit das zu umgehen? Ich benötige leider bei den Kürzeln manchmal mehr als ein Zeichen und dann gibt es diesen Konflickt.

[Kuwer]

Hallo Erich,

teste mal so:

Code:

Sub LangnamenZuordnen()
 With Range("D2", Cells(Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(, 2)
   .Formula = "=INDEX({""Canada"",""United States"",""Mexico""},MATCH(D2,{""C"",""UC"",""M""},0))"
   .Value = .Value
   .SpecialCells(xlCellTypeConstants, 16) = ""
 End With
End Sub

Gruß Uwe

[sharky51]

Hallo Erich,

teste mal so:

Code:

Sub LangnamenZuordnen()
 With Range("D2", Cells(Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(, 2)
   .Formula = "=INDEX({""Canada"",""United States"",""Mexico""},MATCH(D2,{""C"",""UC"",""M""},0))"
   .Value = .Value
   .SpecialCells(xlCellTypeConstants, 16) = ""
 End With
End Sub

Gruß Uwe

Hallo Uwe,

danke....cool...funktioniert perfekt...klasse. So werde ich das übernehmen.
Ich habe natürlich nicht nur 3 Kürzel sondern viel mehr, d.h. der Formel-String wird sehr lang.
Siehst Du da ein Problem darin oder gibt es eine Möglichkeit ca. 20-30 Kürzel in anderer Weise aufzudröseln?

Nochmals vielen Dank!

[Zwergel]

Hi Erich,

mal als Tipp zum Gebrauch des Forums, hier konkret die beiden Antwortbuttons.
Du nimmst ständig den Direkt Antwort Button im Beitragsfenster und dabei wird jedesmal alles aus der Antwort zuvor zitiert, das ist aber nur dann interessant, wenn du dich dort auf konkrete Inhalte beziehen willst. Sonst füllt das alles nur unnötig und macht Beiträge unübersichtlich.

Nutze in allen anderen Fällen bitte den Antwortbutton unterhalb des Fensters, rechts neben Erledigt. Danke.

[Rabe]

Hi,

Du nimmst ständig den Direkt Antwort Button im Beitragsfenster und dabei wird jedesmal alles aus der Antwort zuvor zitiert, das ist aber nur dann interessant, wenn du dich dort auf konkrete Inhalte beziehen willst. Sonst füllt das alles nur unnötig und macht Beiträge unübersichtlich.

Nutze in allen anderen Fällen bitte den Antwortbutton unterhalb des Fensters, rechts neben Erledigt. Danke.

oder kürze das Zitat auf die relevanten Teile. Das ist nämlich auch möglich!

[RPP63]

Hi!
Dann mache ich das doch direkt mal ... :17:

Ich habe natürlich nicht nur 3 Kürzel sondern viel mehr, d.h. der Formel-String wird sehr lang.
Siehst Du da ein Problem darin oder gibt es eine Möglichkeit ca. 20-30 Kürzel in anderer Weise aufzudröseln?

@Erich:
Erstelle Dir eine Verweistabelle, greife im Code auf sie zu und schreibe sofort den Verweis in die Zelle.
Dann brauchst Du auch nicht erst eine Formel in die Zelle schreiben und sie danach durch ihren Wert ersetzen.
(Womit wir wieder bei Günthers Beitrag #2 wären, halt nur per Code und nicht per Formel)

Gruß Ralf