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.

Originally Posted by Leith Ross

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

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.

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!

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...