[mso] Re: Excel-A Tough One
- From: "Green" <1z@xxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Mon, 3 Nov 2008 19:23:59 +0100
Hi Herbert,
I felt a bit guilty so I put the following together.
It should get you started.
It will process the selected cells in a column and separate them out into
the next column.
HTH
Lisa
Sub subSeperate()
Dim rlCell As Range
Dim ilN As Integer
Dim ilM As Integer
Dim slIn As String
Dim slOut As String
Dim slArray() As String
Dim rlLastCell As Range
Dim ilThisColumn As Integer
Dim ilNextColumn As Integer
Dim ilCurrentRow As Integer
Dim slWhat As String
Dim ilDash As Integer
Dim ilLow As Integer
Dim ilHigh As Integer
' Find the "next" cell in the next column.
ilThisColumn = Selection.Column
ilNextColumn = ilThisColumn + 1
ilCurrentRow = 1
Do
If Cells(ilCurrentRow, ilNextColumn).Value = "" Then
Exit Do
End If
ilCurrentRow = ilCurrentRow + 1
Loop
ilN = 0
For Each rlCell In Selection
slIn = rlCell.Value
If slIn <> "" Then
' Get the first bit and chop it off
slArray = Split(slIn)
slWhat = slArray(0)
slIn = Trim(Replace(slIn, slWhat, ""))
' Split the rest up by comma.
slArray = Split(slIn, ",")
For ilN = 0 To UBound(slArray)
slIn = Trim(slArray(ilN))
ilDash = InStr(slIn, "-")
If ilDash > 0 Then
' Range.
'Get the first and last numbers.
ilLow = CInt(Mid(slIn, 1, ilDash - 1))
ilHigh = CInt(Mid(slIn, ilDash + 1))
For ilM = ilLow To ilHigh
slOut = slWhat & " " & ilM
'Put it in the next column.
Cells(ilCurrentRow, ilNextColumn).Value = slOut
ilCurrentRow = ilCurrentRow + 1
Next ilM
Else
' Simple.
slOut = slWhat & " " & slIn
'Put it in the next column.
Cells(ilCurrentRow, ilNextColumn).Value = slOut
ilCurrentRow = ilCurrentRow + 1
End If
Next ilN
End If
Next rlCell
' **************************************************************
End Sub
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
