Depron
02-04-2021, 01:33 AM
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
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