ExcelBanter - Serial Order

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Serial Order (https://www.excelbanter.com/excel-programming/404693-serial-order.html)

This a part from may UDF, my question if I want to put the same serial
number regardless the order eg.( "D10604" And rng < "D11201" And rng <
"D11202" And rng < "D11203" And rng < "D11204") I want to put in any order
there is any code to do that, because if Im not put in serial order it will
not give me the perfect result.

my UDF like
Function Sector(rng As Variant) As Variant

If rng Like "D*" And rng < "D10601" And rng < "D10602" And rng <
"D10603" And rng < "D10604" And rng < "D11201" And rng < "D11202" And rng
< "D11203" And rng < "D11204" And rng < "D11205" And rng < "D11210" And
rng < "D11211" And rng < "D11214" And rng < "D11215" And rng < "D11217"
And rng < "D11218" And rng < "D11219" And rng < "D11220" And rng <
"D11221" And rng < "D11222" And rng < "D11403" And rng < "D11501" And rng
< "D11701" And rng < "D11702" And rng < "D11705" And rng < "D11706" And
rng < "D11808" And rng < "D11901" And rng < "D11902" And rng < "D12001"
And rng < "D12002" And rng < "D12501" And rng < "D13001" And rng <
"D13005" And rng < "D20401" And rng < "D20501" And rng < "D20503" And rng
< "D20504" And rng < "D20601" And rng < "D30103" And rng < "D40203" And
rng < "D40204" And rng < "D50101" "F10402" Then
Sector = "JOHNSON"

Regards

Yousoft-

I'm not sure I fully understand your intention, but I'd put all those
matchable values in an array rather than checking each one individually-
something like the following (aircode)

Function Sector(rng As Variant) As Variant

TempMatch = Array("D10601", "D10602", "D10603", "D10604", "D11201",
"D11202", _
"D11203", "D11204", "D11205", "D11210")

IsMatchable = Application.Match(rng, TempMatch, False)

If rng Like "D*" And IsError(IsMatchable) Then
'do stuff here
End If

End Function

HTH,
Keith

"Yousoft" wrote in message
...
This a part from may UDF, my question if I want to put the same serial
number regardless the order eg.( "D10604" And rng < "D11201" And rng <
"D11202" And rng < "D11203" And rng < "D11204") I want to put in any
order
there is any code to do that, because if I'm not put in serial order it
will
not give me the perfect result.

my UDF like
Function Sector(rng As Variant) As Variant

If rng Like "D*" And rng < "D10601" And rng < "D10602" And rng <
"D10603" And rng < "D10604" And rng < "D11201" And rng < "D11202" And
rng
< "D11203" And rng < "D11204" And rng < "D11205" And rng < "D11210"
And
rng < "D11211" And rng < "D11214" And rng < "D11215" And rng <
"D11217"
And rng < "D11218" And rng < "D11219" And rng < "D11220" And rng <
"D11221" And rng < "D11222" And rng < "D11403" And rng < "D11501" And
rng
< "D11701" And rng < "D11702" And rng < "D11705" And rng < "D11706"
And
rng < "D11808" And rng < "D11901" And rng < "D11902" And rng <
"D12001"
And rng < "D12002" And rng < "D12501" And rng < "D13001" And rng <
"D13005" And rng < "D20401" And rng < "D20501" And rng < "D20503" And
rng
< "D20504" And rng < "D20601" And rng < "D30103" And rng < "D40203"
And
rng < "D40204" And rng < "D50101" "F10402" Then
Sector = "JOHNSON"

Regards

THANK YOU Sir for your help actually, your solution it is useful for me if
its wok, but I try as shown below but its not working I dont know where is
the problem, and another thing as you can see in the first Array I want
remove all those number from €œD€ and in second Array I want add Tow Number
from sector €œF€ to give me the result all number from D and F who belonged to
€œJohnson€ so in second Array I try to change false to true for adding those
two number , it correct or not?. I will be very appreciated for you help.

My UDF as following:

Function SECTORR(rng As Variant) As Variant

TempMatch = Array("D10601", "D10602", "D10603", "D10604", "D11201" _
, "D11202", "D11203", "D11204", "D11205", "D11210", "D11211", "D11214" _
, "D11215", "D11217", "D11218", "D11219", "D11220", "D11221", "D11222" _
, "D11403", "D11501", "D11701", "D11702", "D11705", "D11706", "D11808", _
"D11901", "D11902", "D12001", "D12002", "D12501", "D13001", "D13005", _
"D20401", "D20501", "D20503", "D20504", "D20601", "D30103", _
"D40203", "D40204", "D50101", "D60301", "D60302", "D60303", _
"D60304", "D60305", "D60401", "D60402", "D60403", "D60404", _
"D60405", "D60406", "D60407", "D60408", "D60501") _

IsMatchable = Application.Match(rng, TempMatch, False)

If rng Like "D*" And IsError(IsMatchable) Then
SECTORR = "JOHNSON"
End If

TempMatch = Array("F10401", "F10402")
IsMatchable = Application.Match(rng, TempMatch, True)
If rng Like "F*" And IsError(IsMatchable) Then
SECTORR = "JOHNSON"
End If

End Function

Thanks & Regards

"Keith R" wrote:

Yousoft-

I'm not sure I fully understand your intention, but I'd put all those
matchable values in an array rather than checking each one individually-
something like the following (aircode)

Function Sector(rng As Variant) As Variant

TempMatch = Array("D10601", "D10602", "D10603", "D10604", "D11201",
"D11202", _
"D11203", "D11204", "D11205", "D11210")

IsMatchable = Application.Match(rng, TempMatch, False)

If rng Like "D*" And IsError(IsMatchable) Then
'do stuff here
End If

End Function

HTH,
Keith

"Yousoft" wrote in message
...
This a part from may UDF, my question if I want to put the same serial
number regardless the order eg.( "D10604" And rng < "D11201" And rng <
"D11202" And rng < "D11203" And rng < "D11204") I want to put in any
order
there is any code to do that, because if I'm not put in serial order it
will
not give me the perfect result.

my UDF like
Function Sector(rng As Variant) As Variant

If rng Like "D*" And rng < "D10601" And rng < "D10602" And rng <
"D10603" And rng < "D10604" And rng < "D11201" And rng < "D11202" And
rng
< "D11203" And rng < "D11204" And rng < "D11205" And rng < "D11210"
And
rng < "D11211" And rng < "D11214" And rng < "D11215" And rng <
"D11217"
And rng < "D11218" And rng < "D11219" And rng < "D11220" And rng <
"D11221" And rng < "D11222" And rng < "D11403" And rng < "D11501" And
rng
< "D11701" And rng < "D11702" And rng < "D11705" And rng < "D11706"
And
rng < "D11808" And rng < "D11901" And rng < "D11902" And rng <
"D12001"
And rng < "D12002" And rng < "D12501" And rng < "D13001" And rng <
"D13005" And rng < "D20401" And rng < "D20501" And rng < "D20503" And
rng
< "D20504" And rng < "D20601" And rng < "D30103" And rng < "D40203"
And
rng < "D40204" And rng < "D50101" "F10402" Then
Sector = "JOHNSON"

Regards