回答待ってる間に両パターンかけてしまったので貼っておきます。
格納先変数をそれぞれ分ける場合。
VBA
1Sub test()
2 Dim str As String: str = "(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55)"
3 Dim str1 As String
4 Dim str2 As String
5 Dim str3 As String
6 Dim str4 As String
7 Dim str5 As String
8 Dim str6 As String
9 Dim cols As Variant
10 Dim temp() As Variant
11 ReDim Preserve temp(9)
12
13 cols = Split(Mid(str, 2, Len(str) - 2), ",")
14
15 For i = 0 To UBound(cols)
16 temp(i Mod 10) = cols(i)
17 If i Mod 10 = 9 Or i = UBound(cols) Then
18 ReDim Preserve temp(i Mod 10)
19 Select Case Application.WorksheetFunction.RoundDown(i / 10, 0)
20 Case 0: str1 = "(" & Join(temp, ",") & ")"
21 Case 1: str2 = "(" & Join(temp, ",") & ")"
22 Case 2: str3 = "(" & Join(temp, ",") & ")"
23 Case 3: str4 = "(" & Join(temp, ",") & ")"
24 Case 4: str5 = "(" & Join(temp, ",") & ")"
25 Case 5: str6 = "(" & Join(temp, ",") & ")"
26 End Select
27 ReDim temp(9)
28 End If
29 Next
30End Sub
格納先を配列にする場合。
VBA
1Sub test2()
2 Dim str As String: str = "(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55)"
3 Dim str2(5) As String
4 Dim cols As Variant
5 Dim temp() As Variant
6 ReDim Preserve temp(9)
7
8 cols = Split(Mid(str, 2, Len(str) - 2), ",")
9
10 For i = 0 To UBound(cols)
11 temp(i Mod 10) = cols(i)
12 If i Mod 10 = 9 Or i = UBound(cols) Then
13 ReDim Preserve temp(i Mod 10)
14 str2(Application.WorksheetFunction.RoundDown(i / 10, 0)) = "(" & Join(temp, ",") & ")"
15 ReDim temp(9)
16 End If
17 Next
18End Sub