Extracting and sorting data from a strand in VBA

[Hawkwing]

I'm trying to take a strand, extract some data (connector type), and sort that data to determine which comes first. For example, given the strands
VS IM SC LC SM DPX PVC 2LBL
and
5m FDDI ST 62/125 MULTIMODE FIBER
I want to write "SC" AND "FDDI" into a column called "ConA" and "LC" and "ST" into column "ConB". Several different connector types exist, different formats and word orders are used in the strands, and I have about 40,000 of these to sort through.

My problem is that I can't write values to a cell in a UDF. I've gotten my code to work in a subroutine, but when I put the sub into a function it doesn't work. I started using VBA a few days ago after exams ended, so I'm probably missing something obvious. Here's my sub; "s1" is the strand received from the UDF. Any suggestions on how to utilize the code in a UDF or on alternate sorting methods?

Code:

Sub SortConn(s1)

Range("Z1").Value = InStr(s1, " SC ") 'Place search values into cells for sorting
Range("Z2").Value = InStr(s1, " LC ")
Range("Z3").Value = InStr(s1, " ST ")
Range("Z4").Value = InStr(s1, " FC ")
Range("Z5").Value = InStr(s1, "SCA")
Range("Z6").Value = InStr(s1, "FCA")
Range("Z7").Value = InStr(s1, "LCA")
Range("Z8").Value = InStr(s1, "MTRJ")
Range("Z9").Value = InStr(s1, "MTRU")
Range("Z10").Value = InStr(s1, "MTP")
Range("Z11").Value = InStr(s1, "SMA")
Range("Z12").Value = InStr(s1, "LCU")
Range("Z13").Value = InStr(s1, "BLUNT")
Range("Z14").Value = InStr(s1, "FDDI")

Range("Z1:Z14").Select 'Sort the 2nd conn as cell Z1 and the 1st conn as cell Z2
ActiveWorkbook.Worksheets("COOIS_cleaned.txt").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("COOIS_cleaned.txt").Sort.SortFields.Add Key:=Range _
("Z1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("COOIS_cleaned.txt").Sort
.SetRange Range("Z1:Z14")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[Firefly2012]

You won't be able to write code that affects other cells (including sorting) if the code is instigated from a UDF entered into a worksheet cell. UDFs entered into cells are only supposed to return a value to that cell - they shouldn't create side effects (ie modify any other cells or objects).

What is a 'strand'? What is the full purpose of your code, and do you have to have it in a UDF rather than being activated eg by a button on the worksheet?

 

[Hawkwing]

By "strand" I simply meant the string of characters in a cell, such as the two examples I gave. The purpose of my code is to take a jumbled phrase and separate it into useful cells that can be used in a table to anticipate how much of a certain product will be sold in 2012. To use my previous example,
VS IM SC LC SM DPX PVC 2LBL
VS is the series type, 1M is the cable length, SC and LC are connector types, DPX indicates a duplex connector, and PVC is the material type. I want to extract all this data into different cells so a table can be used. I've figured out how to do this for everything except connector type. It's tricky because I need to know which connector comes first: thus the sorting. I need to be able to use the same method for thousands of lines, so I figured a user-defined function where all I changed was the text string input (E17, E24, etc.) and which connector I wanted (1 or 2) was my best option. If I can do this without writing anything to the worksheet or using a UDF that would be fine. I just don't know how to do so. Does that clarify my issue?