+ Reply to Thread
Results 1 to 6 of 6

Comma separated string

  1. #1
    Registered User
    Join Date
    01-01-2010
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    34

    Comma separated string

    Recently you helped me with a macro which searched all my Excel worksheets for a named range. The result was a text string containing the sheet numbers like "5,6,7,9". Please note the "" around the string and the comma's.

    Quote Originally Posted by Leith Ross View Post
    Hello ESF,

    This macro will return the worksheet numbers as a comma separated string. Later, you can then use the Split function to separate the sheet numbers into an array.
    Please Login or Register  to view this content.
    My next step was to run another action on each one of these sheets with the help of the split command. This was achieved by a code (for example) like this:

    Please Login or Register  to view this content.

    Here comes my problem: I would like to create a formula summing all the cells of this string. So in this example "='Sheet 5'!H13+'Sheet 6'!H13+'Sheet 7'!H13+'Sheet 9'!H13"
    Therefore I tried:
    Please Login or Register  to view this content.
    How do I use the string with "" and , (comma's) into a formula? On the one hand I need to split them again for creating the cell reference and on the other hand I can use the full string because I need all the sheets anyway.

    I hope someone can help me. Thank you in advance.

    Erik

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Comma separated string

    Can you please explain what you are after ?
    I fear you try to accomplish something in a too complicated way that may be a builtin facility in Excel.


    What will you see using these ?
    Please Login or Register  to view this content.
    Last edited by snb; 01-20-2011 at 06:29 AM.



  3. #3
    Registered User
    Join Date
    01-01-2010
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    34

    Re: Comma separated string

    Unfortunately I receive a compile error (even if I remove the double 'then Then') with your suggestion.
    The code by Leith Ross called
    Please Login or Register  to view this content.
    works fine. This part is used by me in other macro's.

    The solution could be simple indeed. But... I am a real beginner in VBA...

    In short: the function GetSheetNumbers replies a string to me with the worksheets I need to create a sum with (e.g. "2,3,4,7").
    Now my target is to sum a large list of cells from all these sheets.
    =SUM('Sheet 2'!H13+'Sheet 3'!H13+... etc)

    How to split the string "x,y,z" including " and , into =SUM(Sheet(x)H13+Sheet(y)H13+...). I don't know how to remove the " and comma's and use the numbers separately.

  4. #4
    Registered User
    Join Date
    01-01-2010
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    34

    Re: Comma separated string

    Let me try to rephrase and simplify the question.

    How to use a string called GetSheetNumbers "2,3,4,n" as the sheetnumber of a formula?

    Please Login or Register  to view this content.

    How to replace my wrong text GetSheetNumbers(first).name with the proper code?

    Any suggestions would be highly appreciated!

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Comma separated string

    PHP Code: 
    =SOM(Blad1:Blad3!H13

  6. #6
    Registered User
    Join Date
    01-01-2010
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    34

    Re: Comma separated string

    Thank you for your swift reply.

    Unfortunately this doesn't help me. First of all the string can vary in length, secondly the string is not always contiguous. And furthermore I don't know how to use the full string in separate sheet index numbers...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1