Split array and re join

mdo8105

Board Regular
Joined
Nov 13, 2015
Messages
83
I have an array that I am able to split and get the individual text in it, then I take each string and find match on another sheet and grab the Id in the next column. I want to take the ID that I find and re join into an array.
Code:
v1 = Split(ws1.Cells(r, 7), ",")For i = 0 To UBound(v1)
           v2 = Application.WorksheetFunction.Index(ws2.Range("D:D"), Application.WorksheetFunction.Match(v1(i), ws2.Range("C:C"), 0))
           
          
        
        Next i
I want to take all that is returned in v2 and make an array delimited by commas and return back into a cell. I've Join, but I get a type mismatch with v2 variable.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You can just concatenate the results as you go, like:
Rich (BB code):
Dim str1 as String
v1 = Split(ws1.Cells(r, 7), ",")
For i = 0 To UBound(v1)
    v2 = Application.WorksheetFunction.Index(ws2.Range("D:D"), Application.WorksheetFunction.Match(v1(i), ws2.Range("C:C"), 0))
    str1 = str1 & "," & v2
Next i
Cells(r, 8) = Mid(str1, 2)

If you want to use Join, something like:
Rich (BB code):
Dim Str1() as string

v1 = Split(ws1.Cells(r, 7), ",")
Redim Str1(0 to UBound(v1)) 
For i = 0 To UBound(v1)
     Str1(i) = Application.WorksheetFunction.Index(ws2.Range("D:D"), Application.WorksheetFunction.Match(v1(i), ws2.Range("C:C"), 0))
Next i
Cells(r, 8) = Join(Str1,",")
 
Last edited:
Upvote 0
How about something like
Code:
   Dim v1 As Variant, v2 As Variant
   Dim v3 As String
   Dim i As Long
   
   v1 = Split(Ws1.Cells(r, 7), ",")
   For i = 0 To UBound(v1)
      v2 = Application.Index(Ws2.Range("D:D"), Application.Match(v1(i), Ws2.Range("C:C"), 0))
      If Not IsError(v2) Then
         If v3 = "" Then v3 = v2 Else v3 = v3 & "," & v2
      End If
   Next i
   Ws1.Cells(r, 8).Value = v3
   v3 = ""
 
Upvote 0

Forum statistics

Threads
1,226,250
Messages
6,189,871
Members
453,577
Latest member
t928super

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top