How to Index Multiple Values into a Single Cell

[kyzyfzh]

Hi Guys,

I am trying to get stock qty from Sheet(SOH), then update them to Sheet(Report) soh column. However, I am having a issue to index Multiple Values into a Single Cell. Any help would be greatly appreciated!

Sub Fillstock_Neworder_Test()

    'Get qty, from SOH to Report'
    
    Dim i, lr, sold As Long
    Dim soh As Variant
    Dim sku, soh_str As String

    lr = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lr
        sku = Sheets("Report").Cells(i, "B").Value
        soh = Application.IfError(Application.Index(Sheets("SOH").Range("A:Z"), _
            Application.Match(sku, Sheets("SOH").Range("A:A"), 0), 2), 0)
        soh_str = ": " & soh  'Get SOH String
  
    
        Sheets("Report").Cells(i, "C").Value = soh_str

    Next i
   
    End Sub

 

[kyzyfzh]

TestAAA.xlsm
	A	B	C
1	Order ID	sku	soh
2	R-17-09209-06644	CAB-ALO-C603PR CAB-ALO-C602GN CAB-ALO-C602WH CAB-ALO-C603RD CAB-ALO-C61.5PR	: 0 : 0 : 0 : 0 : 0 : 1
3	R-18-09208-69480	PHC-OB-77-85801	: 8
4	R-22-09207-14580	PHC-OB-77-85527	: 1
Report

TestAAA.xlsm
	A	B
1	sku	qty
2	CAB-ALO-C603PR	0
3	CAB-ALO-C602GN	7
4	CAB-ALO-C602WH	0
5	CAB-ALO-C603RD	5
6	CAB-ALO-C61.5PR	1
7	SC-ORI-SC2-BK	0
8	PHC-OB-77-85527	1
9	MB-SIM-EC415	1
10	SP-OB-77-88850	1
11	PHC-OB-77-85801	8
12	PHC-OB-77-62542	1
13	SP-PG-P2786	9
14	CAB-8W-C5-ES100	1
15	CAB-8W-C5-ES100	3
16	PBK-CYT-CY3483P	0
17	PHC-OB-77-83551	1
18	SDC-SD-32GFXSB	1
19	CAB-ALO-1M13B	2
SOH