Macro/VBA to compare two sheets and returns multiple results

[urskrishna18]

Hi, I am a beginner in writing macros.
I have 2 sheets, sheet1 and sheet2.
Sheet1 contains the lookup value in Column A (Col1) and Sheet2 contains the lookup array.
The requirement is lookup value in Column A in Sheet1 to be searched in Sheet2 and return the result of column B and Column C for all the matched values .
The returned values to be stored in Column C of Sheet1. Below are screenshots

Sheet1:

Col1	Col2	Col3
A	1​	<Result of matched values to be placed here>
B	2​
C	3​
D	4​

Sheet2:

Look1	Look2	Look3
A	A	1/28/2022​
B	A	1/28/2022​
A	A	1/28/2022​
C	A	1/28/2022​
D	I	1/28/2022​
B	I	1/28/2022​
A	I	1/28/2022​
E	A	1/28/2022​

I have tried the below VBA/Macro but the issue is it is only returning the last matched row instead of all the matched values:

Like for Value "A" in Sheet1.Col1 there are 3 values in Sheet2, i would require all 3 instead of just the last match value.

Code i managed to write:

Sub searchval()

Dim a()     As Variant
Dim str()   As String
Dim i       As Long
Const delim As String = "|"

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

Set ws = Sheets("Sheet1")
    
With ws
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
    With Sheets("Sheet2")
        
        i = .Range("C" & .Rows.Count).End(xlUp).Row
        a = .Range("A1:C" & i).Value
        
        For i = LBound(a, 1) To UBound(a, 1)
            dic(a(i, 1)) = a(i, 2) & delim & a(i, 3)  
        Next i
        
    End With

    With Sheets("Sheet1")
    
        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        
            str = Split(dic(.Range("A" & i).Value), delim)
           
            .Cells(i, LC ).Resize(, 1).Value = str
            
            Erase str
            
        Next i
        
    End With

Set dic = Nothing
Erase a

End Sub

Can someone please help me here?

 

[urskrishna18]

​

No such issue on my side …​

If the cell values are like below (like if something like even borders) then there is no error (table1) but if only one row without border or anything (table2) then i am getting that error. I tested this with the sheet i provided with your code and got the same issue.

Table1:

Col1	Col2	Col3
A	1

Table2: (where type mismatch error is coming)

Col1	Col2	Col3
A	1