Error in a existing Macro used for compare the Comma Separated Values present in 2 columns

Hello Guys,

I need help in removing an error in an existing Macro. I am using a Macro which compares the Comma Separated Values present in 2 different columns.

There are 2 sheets in attached file. In Sheet 1 macro is comparing the comma separated values present in Column C with Column A and if value is same then it will generate 0 and if value is unique then it will generate 1 in Column D. The macro is working fine in Sheet1.

But, if I run the same macro in Sheet2 for same purpose the macro is generating “Type Mismatch - Run Time 13” error.

I need help to modify the macro which works in both conditions either of Sheet1 or Sheet2.

Thanks,
MG

Manish,
You have not got any value in column A to concacnate if the range start from A2

Sub MatchingCommaSplitValues()
Dim s$, sp, x, i&, j&, k&, y, LR&
s = Join(Application.Transpose(Range("A1", Cells(Rows.Count, 1).End(xlUp))), ",")
LR = Cells(Rows.Count, 3).End(xlUp).Row
If LR <= 1 Then Exit Sub
x = Range("C1", Cells(Rows.Count, 3).End(xlUp)).Value
For i = 1 To UBound(x)
    If Len(x(i, 1)) Then
        sp = Split(x(i, 1), ",")
        k = 0
        For j = 0 To UBound(sp)
            If InStr(s, Trim(sp(j))) = 0 Then k = k + 1
        Next j
    If k > 1 Then
        x(i, 1) = 1
    Else
    x(i, 1) = k
    End If
    
    End If
Next i
Range("D2").Resize(i - 1).Value = x

End Sub

Hello AB33,

Thanks for reply.

I had tried to run your suggested Macro but it is throwing result in wrong rows of Volumn D.

Please find the attached file for seeing the result.

Thanks,
MG

MG,
I have not tested the code as you did not have any data to test it on. Please add some output data, so that I can see what is the issue.