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