VBA Split Cell to Array + VLookUp for Array

[Depron]

Hi,

I have an Excel document with 2 Worksheets.
In worksheet 1 I have a string cell with a string that is splitted into an array = myarray.

Cell content, e.g.: L1, L2, L3

For each of the entries in myarray I need to a VLookup in worksheet 2. Unfortunaltey my output is only for the first entry of each array.

Any idea?

Thanks

Sub SplitandCopy()


Dim lastrow As Long
Dim splitstring As String
Dim myarray() As String
Dim MyStringVar1 As String


Set ws1 = ThisWorkbook.Sheets("FMECA")
Set ws2 = ThisWorkbook.Sheets("mitigation_actions")


lastrow = Cells(Rows.Count, 1).End(xlUp).Row


ws1.Cells(1, 2).Value = lastrow






For a = 3 To lastrow


splitstring = ws1.Cells(a, 17).Value


myarray = Split(splitstring, ",")






    For i = 0 To UBound(myarray)
        


          On Error Resume Next
      MyStringVar1 = Application.WorksheetFunction.VLookup(myarray(i), ws2.Range("$A:$B"), 2, False)
    
      ws1.Cells(a, i + 50).Value = myarray(i)
      ws1.Cells(a, i + 55).Value = MyStringVar1


    Next
    Next
    
End Sub

Capture.jpgTable.jpg

[snb]

Bitte, lade mal eine Beispieldatei hoch.
In the picture you posted the table isn't in 1 cell.

[Depron]

Guten Morgen.

anbei ein Minibeispiel.
Im Beispiel ist eine Erläuterung was das Makro machen soll.

Besten Dank im Voraus

Beispiel.xlsm

[p45cal]

MyStringVar1 = Application.VLookup(CLng(myarray(i)), ws2.Range("$A:$B"), 2, False)
maybe.

[Depron]

MyStringVar1 = Application.VLookup(CLng(myarray(i)), ws2.Range("$A:$B"), 2, False)
maybe.

Hi, thanks for the suggestion. Unfortunately not the solution.

I checked the loops, the counting of the variables is correct. Nevertheless the result from VLookup didn´t change...
Is the example helpul?

BR

[p45cal]

Then you need to attach a representative workbook; I'd be guessing, probably wrongly again, as I have so far.

[Depron]

Hi,

I did a new example file. Please check attachements.

The job of the macro shall be to split the IDs in column A into an array. For each of the array elements the corresponding color (Sheet 2) shall be looked up and stored into Sheet 1 column B.

Thanks in advance

[snb]

But why using 1 cell for several values ? (ID's as well as colours)
You'd better split the values in separate columns.
Now you need to solve a 'problem' your created yourself by not properly structuring the worksheet.

[p45cal]

In the attached your macro adjusted and a user defined function. See columns B & C of Sheet1

[snb]

Sub M_snb()
  sn = Sheet2.Cells(1).CurrentRegion
   
  For j = 2 To UBound(sn)
    Sheet1.Columns(1).Replace sn(j, 1), sn(j, 2), 2
  Next
End Sub