Macro/VBA to compare two sheets and returns multiple results

urskrishna18

New Member
Joined
Jan 28, 2022
Messages
9
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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:
Col1Col2Col3
A
1​
<Result of matched values to be placed here>
B
2​
C
3​
D
4​

Sheet2:

Look1Look2Look3
AA
1/28/2022​
BA
1/28/2022​
AA
1/28/2022​
CA
1/28/2022​
DI
1/28/2022​
BI
1/28/2022​
AI
1/28/2022​
EA
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:

VBA Code:
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?
 
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:
Col1Col2Col3
A1

Table2: (where type mismatch error is coming)

Col1Col2Col3
A1
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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