HELP! Number length macro

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

something like this?

Sub findbestmatch()
On Error Resume Next
With Sheets("sheet10").Columns(1)
x = .Find("12345", lookat:=xlWhole).Address
x = .Find("1234", lookat:=xlWhole).Address
x = .Find("123", lookat:=xlWhole).Address
x = .Find("12", lookat:=xlWhole).Address
End With
MsgBox x
End Sub

 

[Guest]

That works perfectly! Thankyou!!

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

 

[Guest]

Hi Tom,

How would I loop through that statement? Ie do the check for range A1:A10
if I had a list of numbers to check?

Thanks!
Jas