Comparison of two columns

[newvbie]

Hi there. I got two columns ("A" & "C") of values to be compared. If same values are identified in the two columns, an indicator "Y" will be inserted in the cell of Column B corresponding to column A. The problem is: Value in Column A is string (e.g. 0000063) whereas in column C, values are numeric (e.g. 63). I have tried to define values of the both columns to be "val" but it seems that the comparison has been erroneous. Should I first convert the values of column C with leading zero as string? And how can do that in vba?

Column A
0000063
0001500
0000624
0000954
0001302
0001592

Column B
31239
57744
131976
237549
269330
295603

my script:

Dim CompareRange As Variant, x As Variant, y As Variant
LR1 = Range("A" & Rows.Count).End(xlUp).Row
LR2 = Range("C" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A2:A" & LR1)
For x = 2 To LR2
For y = 2 To LR1
If Trim(Val(Range("C" & x).Value)) = Trim(Val(Range("A" & y).Value)) Then
Range("B" & y).Value = "Y"
End If
Next y
Next x

Thanks.

 

[drsarao]

This seems to work for me:

Sub test()
Dim CompareRange As Variant, x As Variant, y As Variant
LR1 = Range("A" & Rows.Count).End(xlUp).Row
LR2 = Range("C" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A2:A" & LR1)
For x = 2 To LR2
    For y = 2 To LR1
        If [COLOR=Red]Range("C" & x) = Val(Range("A" & y)) [/COLOR]Then
            Range("B" & y).Value = "Y"
        End If
    Next y
Next x
End Sub