HELP! Number length macro

[Jas]

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

[Tom Ogilvy]

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

[Jas]

That works perfectly! Thankyou!!

"Tom Ogilvy" wrote:

> 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

[Jas]

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

"Tom Ogilvy" wrote:

> 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

[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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Jas" <Jas@discussions.microsoft.com> wrote in message
news:C3BB6DEC-D2AC-4B99-8D03-110DE15DEA10@microsoft.com...
> 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