Consulting

Results 1 to 9 of 9

Thread: SPLIT TEXT BY MACROS

  1. #1

    SPLIT TEXT BY MACROS

    Hello Forum

    I have this code that split a text, and put a value in a text box,
    Ex:

    ABS1-EN1-FINLAND1 Split in ABS1 or EN1 or FINLAND1
    I want to change it such in order to chose the column that contains the text to split, and in the column to put my result
    Here is the code with the userform,

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Not sure if you are wanting to show something in a userform or just process the data. If the latter, then create two refedit controls and put this in a commandbutton. I just returned the 2nd element of the split array.
    [VBA]Private Sub CommandButton1_Click()
    Dim sCol As Range, rCol As Range
    Dim a() As String, c As Range
    On Error Resume Next
    Set sCol = Range(RefEdit1)
    Set rCol = Range(RefEdit2)
    If sCol Is Nothing Then Set sCol = Range("A1")
    If rCol Is Nothing Then Set rCol = Range("B1")
    For Each c In Range(Cells(2, sCol.Column), Cells(Rows.Count, sCol.Column).End(xlUp))
    a() = Split(c, "-")
    If UBound(a) > 0 Then
    Cells(c.Row, rCol.Column).Value = a(1)
    End If
    Next c
    Unload Me
    End Sub[/VBA]

  3. #3
    Thanks for your help, can i ask you to add also the choice of element to split
    like in my first attempt
    Thank you

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    You could use what you did by getting the option button index.

    In this example, I used a label and a spinbutton control.
    [VBA]Private Sub UserForm_Initialize()
    Label1.Caption = 2
    End Sub

    Private Sub CommandButton1_Click()
    Dim sCol As Range, rCol As Range
    Dim a() As String, c As Range
    On Error Resume Next
    Set sCol = Range(RefEdit1)
    Set rCol = Range(RefEdit2)
    If sCol Is Nothing Then Set sCol = Range("A1")
    If rCol Is Nothing Then Set rCol = Range("B1")
    For Each c In Range(Cells(2, sCol.Column), Cells(Rows.Count, sCol.Column).End(xlUp))
    a() = Split(c, "-")
    If UBound(a) > 0 Then
    Cells(c.Row, rCol.Column).Value = a(Label1.Caption - 1)
    End If
    Next c
    Unload Me
    End Sub

    Private Sub SpinButton1_SpinDown()
    With Label1
    If .Caption = 1 Then Exit Sub
    .Caption = .Caption - 1
    End With
    End Sub

    Private Sub SpinButton1_SpinUp()
    With Label1
    If .Caption = 3 Then Exit Sub
    .Caption = .Caption + 1
    End With
    End Sub[/VBA]

  5. #5

    Exclamation

    Hello,
    Thanks for your help
    i just tested the macros but is not displaying the results in the range that i chose with the ref edit, the result is going always in the second column, or i want it to go in the range i chose

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    I designed it to do use Column B for the results if RefEdit2 was empty.

    You can post the xls if you like.

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Here is your example using my userform2 and code.

  8. #8

  9. #9

    Cross posting , I think

    Hi all,

    teodormircea
    Troubles with your memory, perhaps ?

    http://www.excel-downloads.com/forum/694732-post11.html

    or dont like VBAcrumble 's pie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •