Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Split with an Array (Excel 2003)

    Can anyone help?

    I am attempting to split an array but am experiencing problems. It may be a case of me not seeing the obvious but another set of eyes will really help. The code I have is below, when I run this I get a type mismatch error.

    strCaption = Split(arrCharts(lngCount), ",")

    The array value (arrCharts(lngCount)) is equal to ""Area Chart, 418"

    Have tested the split function on a different string and it appears to work as expected so have no real idea why not in this case. The array is set up as a string.

    Any help greatly appreciated.
    Cheers
    Jack

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Split with an Array (Excel 2003)

    Hi,
    The Split function returns an array but you appear to be trying to assign it to a string variable, hence the type mismatch.
    HTH

    PS You could use <code>strCaption = Split(arrCharts(lngCount), ",")(0)</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Split with an Array (Excel 2003)

    Thanks Rory that worked a treat. Just so I understand though, what is the relevance of the (0) as I need to cycle through the array?

    Many thanks
    Jack

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Split with an Array (Excel 2003)

    The (0) just returns the first value in the array. If you need to loop through then you can use something like:

    <pre> varData = Split(arrCharts(lngCount), ",")
    for n = LBound(varData) to Ubound(varData)
    debug.print varData(n)
    next n
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Split with an Array (Excel 2003)

    I have tested cycling through the array using the (0) at the end of the line of code and it is populating as expected. The count that cycles though the array is after the declared array, so it appears I don't need to change the (0) at the end.

    All I know is it's working fine .... many thanks for your help.
    Jack

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Split with an Array (Excel 2003)

    When you talk about looping through the array are you talking about arrCharts or the array returned by the Split function? If you just want the bit before the comma for each value in arrCharts(), then what you are doing is correct. I thought you meant you wanted to loop through the Split array within the arrCharts loop, which is what my code was for.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Split with an Array (Excel 2003)

    I was trying to cycle through a single array but kept getting problems, as soon as I put the (0) on it worked a treat. Have no idea why I could not cycle through a single array in this way, it seemed to make sense to me but every time I got the mismatch error.

    Well like I say, no idea why it works, but grateful for your help in getting there.

    Cheers
    Jack

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Split with an Array (Excel 2003)

    You are confusing two arrays here.

    You have an array of strings arrCharts. You can loop through the items of this array using something like

    For lngCount = 1 to 10
    ' do something with arrCharts(lngCount) here
    Next lngCount

    For each value of lngCount, arrCharts(lngCount) is a string of the form "Area Chart, 418". You want to extract the first part of this string, before the comma. This can be done in several ways.

    One of them is to use the Split function to split the string arrCharts(lngCount) into an array of individual parts. In other words, the array item arrCharts(lngCount) is turned into an array of its own. Using the example "Area Chart, 418", the result of Split(arrCharts(lngCount)) is an array consisting of 2 elements: "Area Chart" and " 418". The result of Split is always zero-based, so the first item has index 0, the second one has index 1, etc. To refer to the first item of the array returned by Split, you use Split(arrCharts(lngCount))(0).
    You don't loop through the items of this 'inner' array, because you are only interested in the first item, with index 0.
    You can't use strCaption = Split(arrCharts(lngCount)), because the left hand side is a string, and the right hand side is not a string, but an array of strings.

    Another way would have been to use the InStr and Left functions:
    strCaption = Left(arrCharts(lngCount), InStr(arrCharts(lngCount), ",") - 1)

Posting Permissions

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