[mso] Re: Excel-A Tough One

Hey Lisa,

Many thanks, I will give this a try, and will let you know how I get on.

Much appreciated.


Herbert

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Green
Sent: 03 November 2008 18:24
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Excel-A Tough One

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).
http://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
*************************************************************
*************************************************************
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).
http://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: