Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 Split Function (2000)

    I am working on trying to split the content of and existing cell and put the splits into three other cells

    example:
    Bailey Kevin R | Bailey | Kevin | R

    Code:
    Dim Name(2)
    Dim FullName As String

    FullName = Cells(1,1)
    Name = Split(FullName, " ", 3) ' I know that the " " is default but want to declare in case it changes
    Cells(1,2) = Name(0)
    Cells(1,3) = Name(1)
    Cells(1,4) = Name(2)
    End Code

    I get an error - Compile Error cannot assign to an array.

    Says it returns a one dimensional array with substrings.

    What am I missing???

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Split Function (2000)

    If all of the cells that you need to split look like your example, then you don't need a macro to split them. You can do the following:

    1- Select all of the cells that you want to split.

    2- Select "Text to columns" from the Data menu.

    3- Select "Delimited" and click on Next.

    4- Put a check next to "Space" and click on Finish.

    If you really need to do it in VBA code, the the following should do what your code was trying to do:

    <pre>Public Sub SplitName()
    Dim strNames As Variant
    strNames = Split(Cells(1, 1), " ", 3)
    Cells(1, 2) = strNames(0)
    Cells(1, 3) = strNames(1)
    Cells(1, 4) = strNames(2)
    End Sub
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Split Function (2000)

    Thanks for the info. I have to create this for a group of unpredicted users so need it to operate without using the text to columns feature - needs to be lean and clean and one step easy to use.

    I figured out what was my issue - I was declaring strNames as an array rather than a 'true' variant. I was over declaring in other words. Works fine now.

    Thanks again.

Posting Permissions

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