Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Number length macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Number length macro
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").Col umns(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Number length macro
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 "Jas" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Number length macro
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").Col umns(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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Number length macro
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").Col umns(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
length principle of a number within a cell | Excel Discussion (Misc queries) | |||
Maximum length of a number | Excel Worksheet Functions | |||
number length limit | Excel Programming | |||
Formating number to fix length | Excel Programming | |||
Number length headache | Excel Programming |