In Ihren Kommentaren haben Sie erwähnt, dass Sie jetzt Zugriff auf eine Version von Excel haben, auf der VBa ausgeführt werden kann.
Dies ist VBa und macht, was ich denke, du willst. Ich habe Screenshots hinzugefügt.
Ich habe einige Kommentare im Code hinterlassen, der erste Abschnitt muss möglicherweise aktualisiert werden, aber die Kommentare sollten Ihnen helfen.
Denken Sie daran, machen Sie zuerst eine Sicherungskopie Ihrer Datei, da es keine Funktion zum Rückgängigmachen gibt!
Basierend auf Ihren Kommentaren verwende ich die tatsächlichen Arbeitsblattnamen!
Option Explicit Sub WalkThePlank() 'hear ye, only edit this top past of walk the plank 'Remember scurvy sea dog, there is no UNDO so take a copy of the file first as a back up Dim worksheet1 As String worksheet1 = "Image names" 'The name of the work sheet which has only codes Dim worksheet1Column As String worksheet1Column = "A" 'Argghh, the name of the column you use in worksheet1 Dim worksheet2 As String worksheet2 = "LMFD products" 'The name of the worksheet with things like CC972 CC973 (Rear).jpg Dim worksheet2Column As String worksheet2Column = "A" 'Argghh, the name of the column you use in worksheet2 Dim resultsWorksheet As String resultsWorksheet = "LMFD products" 'C'pan, this is where you put the results Dim resultsWorksheetColumn As String resultsWorksheetColumn = "C" 'Argghh, the name of the column you use in worksheet2 'hear ye, walk below and I'll feed ye to the sharks Application.ScreenUpdating = False Dim row As Integer row = 2 'The starting row with values to be looked up Do While (Worksheets(worksheet1).Range(worksheet1Column & row).Value <> "") Dim result As String result = "" Dim lookupValue As String lookupValue = Worksheets(worksheet1).Range(worksheet1Column & row).Value Dim otherRow As Integer otherRow = 2 'The starting row of the .jpg colum Dim startString As String Dim endString As String startString = "" endString = "" Do While (Worksheets(worksheet2).Range(worksheet2Column & otherRow).Value <> "") Dim repoValue As String repoValue = Worksheets(worksheet2).Range(worksheet2Column & otherRow).Value If (InStr(repoValue, lookupValue)) Then 'we got treasure cap'ain If (InStr(repoValue, "(")) Then endString = Trim(endString) & Trim(repoValue) & "|" Else startString = Trim(startString) & Trim(repoValue) & "|" End If End If otherRow = otherRow + 1 Loop 'check on the treasure, will we fine riches If (startString <> "" And endString <> "") Then result = Trim(startString & Left(endString, Len(endString) - 1)) Else If (startString = "" And endString <> "") Then result = Trim(Left(endString, Len(endString) - 1)) End If If (endString = "" And startString <> "") Then result = Trim(Left(startString, Len(startString) - 1)) End If End If Worksheets(resultsWorksheet).Range(resultsWorksheetColumn & row).Value = result ' X Marks the spot row = row + 1 Loop End Sub
Mein Arbeitsblatt1 (bevor die VBa ausgeführt wird)
Und mein Arbeitsblatt2
Und das Ergebnis ist