Sub Checkmatches()
Dim i as Long, s as String
Dim res as Variant
s = Range("A1").Value
for i = len(s) to 1 step -1
res = Application.Match(clng(s),Worksheets("Sheet2").Columns(1),0)
if not iserror(res) then
msgbox "found " & s & " at row " & res
exit for
else
if len(s) > 1
s = left(s,len(s)-1)
end if
end if
Next i
End Sub

--
Regards,
Tom Ogilvy


"Jas" wrote:

> Hi,
>
> I need a macro that will take the input from cell A1 (numbers) and will keep
> dropping a digit until it finds a match on another spreadsheet (assume its
> all in column A:A). So, I need it to do the following
> Cell A1 "12345"
> A2 "1234"
> A3 "123"
> A4 "12" and it needs to stop at 2 digits.
>
> The numbers in cells A2-A4 are checked in Sheet2 and if there is a match
> then I need a message box saying "Found", or something.
>
> How can I do this?
> Thanks
> Jas