記憶データ成形＋列挙された項目名をvba配列に

Option Explicit

Const STRING_LINE = 32
Const DATA_COL = 4
Const MINUTE_NUM = 15
Const ROW_GAP = 130
Const ELEM_NUM = 26
Const PASTE_ROW = 2
Const PASTE_COL = 3
Const TARGET_ROW = PASTE_ROW                                                '見やすいように貼り付ける別シート
Const TARGET_COL = PASTE_COL                                                '見やすいように貼り付ける別シート
Const PASTE_ROW_CONVERTED = PASTE_ROW
Const PASTE_COL_CONVERTED = PASTE_COL + 7
Const TARGET_ROW_CONVERTED = TARGET_ROW + ROW_GAP    '見やすいように貼り付ける別シート
Const TARGET_COL_CONVERTED = TARGET_COL                         '見やすいように貼り付ける別シート

Sub caps()

    Call cap_data
    Call cap_converted

End Sub

Sub cap_data()
Dim i As Long
Dim j As Long
Dim copySheetName As String
Dim pasteSheetName As String
Dim indexArray As Variant
Dim convertedIndexArray As Variant
Dim integratedIndexArray As Variant
Dim integratedConvertedIndexArray As Variant

    copySheetName = "data1"
    pasteSheetName = "data2"

    'indexArray = Array("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")
    indexArray = Array("a", "aa", "aaa", "aaaa", "b", "bb", "bbb", "bbbb", "c", "cc", "ccc", "cccc", "ab", "abab", "ba", "baba", "ca", "caca", "ac", "acac", "bc", "bcbc", "cb", "cbcb", "abc", "abcabc", "bca", "bcabca", "cba", "cbacba", "acb", "acbacb")
    integratedIndexArray = Array("l", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "m")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'index
    With Sheets(pasteSheetName)
        For i = 0 To STRING_LINE - 1
            'data_index
            .Cells(TARGET_ROW + i, TARGET_COL + 1) = indexArray(i)
            'integrated_index
            .Cells(TARGET_ROW + i, TARGET_COL - 1) = integratedIndexArray(i)
        Next i
    End With

    'データ
    For j = 1 To DATA_COL
        For i = 1 To MINUTE_NUM
            With Sheets(copySheetName)
                .Range(.Cells(PASTE_ROW + STRING_LINE * (i - 1), PASTE_COL + (j - 1)), .Cells(PASTE_ROW + STRING_LINE * i - 1, PASTE_COL + (j - 1))).Copy
            End With

            With Sheets(pasteSheetName)
                .Range(.Cells(TARGET_ROW + STRING_LINE * (j - 1), TARGET_COL + (i + 1)), .Cells(TARGET_ROW + STRING_LINE * j - 1, TARGET_COL + (i + 1))).PasteSpecial

                'paste_time
                .Cells(TARGET_ROW - 1, TARGET_COL + (i + 1)) = (i - 1) & "分前"
            End With
        Next i

        'integrated_data
        With Sheets(copySheetName)
            .Range(.Cells(PASTE_ROW + STRING_LINE * MINUTE_NUM, PASTE_COL + (j - 1)), .Cells(PASTE_ROW + STRING_LINE * (MINUTE_NUM + 1) - 1, PASTE_COL + (j - 1))).Copy
        End With
        With Sheets(pasteSheetName)
            .Range(.Cells(TARGET_ROW + STRING_LINE * (j - 1), TARGET_COL), .Cells(TARGET_ROW + STRING_LINE * j - 1, TARGET_COL)).PasteSpecial
        End With

        With Sheets(pasteSheetName)
            'index_cap
            .Range(.Cells(TARGET_ROW, TARGET_COL + 1), .Cells(TARGET_ROW + STRING_LINE - 1, TARGET_COL + 1)).Copy
            .Range(.Cells(TARGET_ROW + STRING_LINE * (j - 1), TARGET_COL + 1), .Cells(TARGET_ROW + STRING_LINE * j - 1, TARGET_COL + 1)).PasteSpecial
            'integrated_index_cap
            .Range(.Cells(TARGET_ROW, TARGET_COL - 1), .Cells(TARGET_ROW + STRING_LINE - 1, TARGET_COL - 1)).Copy
            .Range(.Cells(TARGET_ROW + STRING_LINE * (j - 1), TARGET_COL - 1), .Cells(TARGET_ROW + STRING_LINE * j - 1, TARGET_COL - 1)).PasteSpecial
        End With

        'e記憶番号
        With Sheets(pasteSheetName)
            .Cells(TARGET_ROW + STRING_LINE * (j - 1), TARGET_COL - 2) = "e記憶番号" & j
        End With

    Next j

    Sheets(pasteSheetName).Cells(TARGET_ROW - 1, TARGET_COL) = "integrated_data"
    Sheets(pasteSheetName).Cells(TARGET_ROW - 1, TARGET_COL + 2) = "直前データ"

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Sub cap_converted()
Dim i As Long
Dim j As Long
Dim copySheetName As String
Dim pasteSheetName As String
Dim indexArray As Variant
Dim convertedIndexArray As Variant
Dim integratedIndexArray As Variant
Dim integratedConvertedIndexArray As Variant

    copySheetName = "data1"
    pasteSheetName = "data2"

    convertedIndexArray = Array("x", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "y")
    integratedConvertedIndexArray = Array("v", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "w")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'index
    With Sheets(pasteSheetName)
        For i = 0 To ELEM_NUM - 1
            'converted_index
            .Cells(TARGET_ROW_CONVERTED + i, TARGET_COL_CONVERTED + 1) = convertedIndexArray(i)
            'integrated_converted_index
            .Cells(TARGET_ROW_CONVERTED + i, TARGET_COL_CONVERTED - 1) = integratedConvertedIndexArray(i)
        Next i
    End With

    'データ
    For j = 1 To DATA_COL
        For i = 1 To MINUTE_NUM
            With Sheets(copySheetName)
                .Range(.Cells(PASTE_ROW_CONVERTED + ELEM_NUM * (i - 1), PASTE_COL_CONVERTED + (j - 1)), .Cells(PASTE_ROW_CONVERTED + ELEM_NUM * i - 1, PASTE_COL_CONVERTED + (j - 1))).Copy
            End With

            With Sheets(pasteSheetName)
                .Range(.Cells(TARGET_ROW_CONVERTED + ELEM_NUM * (j - 1), TARGET_COL_CONVERTED + (i + 1)), .Cells(TARGET_ROW_CONVERTED + ELEM_NUM * j - 1, TARGET_COL_CONVERTED + (i + 1))).PasteSpecial

                'paste_time
                .Cells(TARGET_ROW_CONVERTED - 1, TARGET_COL_CONVERTED + (i + 1)) = (i - 1) & "分前"
            End With
        Next i

        'integrated_data
        With Sheets(copySheetName)
            .Range(.Cells(PASTE_ROW_CONVERTED + ELEM_NUM * MINUTE_NUM, PASTE_COL_CONVERTED + (j - 1)), .Cells(PASTE_ROW_CONVERTED + ELEM_NUM * (MINUTE_NUM + 1) - 1, PASTE_COL_CONVERTED + (j - 1))).Copy
        End With
        With Sheets(pasteSheetName)
            .Range(.Cells(TARGET_ROW_CONVERTED + ELEM_NUM * (j - 1), TARGET_COL_CONVERTED), .Cells(TARGET_ROW_CONVERTED + ELEM_NUM * j - 1, TARGET_COL_CONVERTED)).PasteSpecial
        End With

        With Sheets(pasteSheetName)
            'index_cap
            .Range(.Cells(TARGET_ROW_CONVERTED, TARGET_COL_CONVERTED + 1), .Cells(TARGET_ROW_CONVERTED + ELEM_NUM - 1, TARGET_COL_CONVERTED + 1)).Copy
            .Range(.Cells(TARGET_ROW_CONVERTED + ELEM_NUM * (j - 1), TARGET_COL_CONVERTED + 1), .Cells(TARGET_ROW_CONVERTED + ELEM_NUM * j - 1, TARGET_COL_CONVERTED + 1)).PasteSpecial
            'integrated_index_cap
            .Range(.Cells(TARGET_ROW_CONVERTED, TARGET_COL_CONVERTED - 1), .Cells(TARGET_ROW_CONVERTED + ELEM_NUM - 1, TARGET_COL_CONVERTED - 1)).Copy
            .Range(.Cells(TARGET_ROW_CONVERTED + ELEM_NUM * (j - 1), TARGET_COL_CONVERTED - 1), .Cells(TARGET_ROW_CONVERTED + ELEM_NUM * j - 1, TARGET_COL_CONVERTED - 1)).PasteSpecial
        End With

        'e記憶番号
        With Sheets(pasteSheetName)
            .Cells(TARGET_ROW_CONVERTED + ELEM_NUM * (j - 1), TARGET_COL_CONVERTED - 2) = "e記憶番号" & j
        End With

    Next j

    Sheets(pasteSheetName).Cells(TARGET_ROW_CONVERTED - 1, TARGET_COL_CONVERTED) = "integrated_data"
    Sheets(pasteSheetName).Cells(TARGET_ROW_CONVERTED - 1, TARGET_COL_CONVERTED + 2) = "直前データ"

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

#coding:utf-8

txt = u"""
あ 
い 
う 
え 
お 
"""

idx_list=[]
for idx in txt.split(u"\n"):
    if len(idx.strip())<1:
        continue
    idx_list.append(idx.strip())

#Array("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")
print 'Array(',
for i,aidx in enumerate(idx_list):
    if i == len(idx_list)-1:
        print '"'+aidx+'"',
    else:
        print '"'+aidx+'",',
print ')'