Serial Order

[Yousoft]

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

 

[Keith R]

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]

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 don’t 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]

Yousoft-

Your syntax assumptions for application.match on your second match is
incorrect. Take a look at the sytax in the help file within Excel (not VBE)
for the match function. True/False is whether you expect an exact match-
which you do, so both of your statements should use 'False' as the third
argument. If the target value (rng) is found in the comparison array, the
statement will return a number indicating the position in the array where it
was found. If it was not found, the statement returns an error. Therefore on
your second match I think you want:

<snip>
TempMatch2 = Array("F10401", "F10402")
IsMatchable = Application.Match(rng, TempMatch2, False)
If rng Like "F*" And Not(IsError(IsMatchable)) Then
SECTORR = "JOHNSON"
End If
<snip>

Just for clarity, I ususally don't re-use variable or array names unless
necessary; for troubleshooting I suggest changing your second array and
associated match to Tempmatch2. That prevents poor syntax from creating
unexpected results- if there is a problem, it is easier to track down where
and why the problem occurs, at least for me.

If that still isn't working, I suggest using a breakpoint and identifying
what value is coming across in rng. Also, How is rng dimmed, and where- are
you using option explicit? In the IsMatchable line, you might have to use
rng.value if you are these are cell values (part numbers) rather than cell
references, but I've assumed that they were cell references.

HTH,
Keith