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



2786027861

[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

27875

[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