Sorting a Text String by Stepping through its components

  • Thread starter Thread starter Izran
  • Start date Start date
I

Izran

Hey guys,

Lucky me!! Someone gave me a text file with multiple data inputs on
each line. I copied the file into excel and I'm trying to sort out the
1st, 2nd, 6th and 9th piece of data for later use. Somehow I am
messing up stepping through the string. I am trying to use the commas
in each line to separate the pieces of data.

Here is an example of some of the lines to be sorted:

5,1813,1700,1720,0,9732,9813,9868,0

10,1809,1763,1740,0,9734,9818,9866,1

15,1806,1757,1726,0,9729,9818,9878,1

20,1801,1744,1760,0,9722,9818,9878,2

I copied this data into column A would liek to place the selected
sorted data in columns BCD & E.


Here is the code I've developed so far (I know some of you may know how
to do this in 2 steps, but the only programming courses I've take were
in C):

Sub Sort()

Dim sMSRData As String
Dim sCurrent As String
Dim sVoltage As String
Dim iString As Integer
Dim sCycle As String
Dim sThermal As String
Dim iRow As Integer
Dim iCommaCount As Integer
Dim iTo As Integer

iRow = 2
iString = 0
iCommaCount = 0
iTo = 0

For iRow = 2 To 3000

iString = 0
iTo = 0

If IsEmpty(Cells(iRow, 1).Value) Then

iRow = 3000

End If



If Not IsEmpty(Cells(iRow, 1).Value) Then

sMSRData = Cells(iRow, 1)
iCommaCount = 0

For iString = 0 To 255

If iCommaCount = 0 Then

iTo = 0

If sMSRData(iString).Value <> "," Then

sCycle(iTo) = sMSRData(iString)

End If

If sMSRData(iString).Value = "," Then

iCommaCount = iCommaCount + 1

End If

End If

If iCommaCount = 1 Then

iTo = 0

If sMSRData(iString).Value <> "," Then

sCurrent(iTo) = sMSRData(iString)

End If

If sMSRData(iString).Value = "," Then

iCommaCount = iCommaCount + 1

End If

End If


If iCommaCount < 5 Then

If iCommaCount >= 2 Then

If sMSRData(iString).Value = "," Then

iCommaCount = iCommaCount + 1

End If

End If

End If


If iCommaCount = 5 Then

iTo = 0

If sMSRData(iString).Value <> "," Then

sVoltage(iTo) = sMSRData(iString)

End If

If sMSRData(iString).Value = "," Then

iCommaCount = iCommaCount + 1

End If

End If

If iCommaCount < 8 Then

If iCommaCount >= 6 Then

If sMSRData(iString).Value = "," Then

iCommaCount = iCommaCount + 1

End If

End If

End If

If iCommaCount = 8 Then

iTo = 0

If sMSRData(iString).Value <> "," Then

sThermal(iTo) = sMSRData(iString)

End If

If sMSRData(iString).Value = "" Then

iString = 255

End If

End If


If iString = 255 Then

Cells(iRow, 2) = sCycle
Cells(iRow, 3) = sCurrent
Cells(iRow, 4) = sVoltage
Cells(iRow, 5) = sThermal


End If


iTo = iTo + 1

Next iString



End If

Next iRow

End Sub





Thank you for any help you can give me,
MPManzi
 
Well, you're asking in the wrong place (this newsgroup is for questions
about Access, the database product that's part of Office Professional), but
Excel has this capability built into it (Text To Columns under the Data
menu).

Excel also has the ability to record macros, so record yourself doing it
manually once, and you'll be able to reuse the macro.
 
Back
Top