Comma separated string

**ESF** · 01-19-2011, 09:15 AM

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.

Function GetSheetNumbers() As String

  Dim Indices As String
  Dim N As Integer
  Dim vName As Name

    For Each vName In ActiveWorkbook.Names

      If vName.Name Like "*Obj_Nr" Then
         N = vName.RefersToRange.Parent.Index
         Indices = IIf(Indices <> "", Indices & "," & N, N)
      End If

    Next vName

    GetSheetNumbers = Indices
    
End Function

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:

    For Each Sht In Split(GetSheetNumbers, ",")
        MsgBox ("The name of the sheet is " & Sheets(Sht + 0).Name)
    Next Sht

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:

For Row = 1 to 85
   Worksheets("Summary").Range("H" & Row).FormulaR1C1 = "='" & Sheets(Sht + 0).Name & "'!RC"
Next Row

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

**snb** · 01-19-2011, 10:24 AM

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 ?

Sub snb()
  msgbox GetsheetNumbers
End Sub

Function GetSheetNumbers() As String
  For Each Nm In ThisWorkbook.Names
    If instr(Nm,"Obj_Nr")>0 Then GetSheetNumbers=GetSheetNumbers & "|" & Nm.Referstorange
  Next
End Function

**ESF** · 01-19-2011, 10:42 AM

Unfortunately I receive a compile error (even if I remove the double 'then Then') with your suggestion.
The code by Leith Ross called

Function GetSheetNumbers() As String

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.

**ESF** · 01-20-2011, 06:27 AM

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?

Range("A1").FormulaR1C1 = "='" & Sheets(GetSheetNumbers(first)).Name & "'!RC+" & Sheets(GetSheetNumbers(second)).Name & "'!RC

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

Any suggestions would be highly appreciated!

**snb** · 01-20-2011, 06:32 AM

PHP Code:


=SOM(Blad1:Blad3!H13)

**ESF** · 01-20-2011, 06:39 AM

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