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
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