Extracting and sorting data from a strand in VBA

Hawkwing

New Member
Joined
Dec 27, 2011
Messages
2
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top