| |
▼Kein さん：
こんばんは、さっそく教えて頂いたVBAコード
>Sub MyData_Split()
> Dim i As Long, j As Long
> Dim Ary1() As String, Ary2() As String
> Dim SpAry As Variant, V As Variant
>
> With Sheets("Sheet1")
> For i = 1 To .Range("A1").End(xlDown).Row
> If IsEmpty(.Cells(i, 2).Value) Then
> ReDim Preserve Ary1(j): Ary1(j) = .Cells(i, 1).Value
> ReDim Preserve Ary2(j): Ary2(j) = ""
> j = j + 1
> Else
> If Len(.Cells(i, 2).Value) = 1 Then
> ReDim Preserve Ary1(j): Ary1(j) = .Cells(i, 1).Value
> ReDim Preserve Ary2(j): Ary2(j) = .Cells(i, 2).Value
> j = j + 1
> Else
> SpAry = Split(.Cells(i, 2).Value, ",")
> For Each V In SpAry
> ReDim Preserve Ary1(j): Ary1(j) = .Cells(i, 1).Value
> ReDim Preserve Ary2(j): Ary2(j) = V
> j = j + 1
> Next
> Erase SpAry
> End If
> End If
> Next i
> End With
> With Sheets("Sheet2")
> .Cells(1, 1).Resize(UBound(Ary1) + 1).Value = _
> WorksheetFunction.Transpose(Ary1)
> .Cells(1, 2).Resize(UBound(Ary2) + 1).Value = _
> WorksheetFunction.Transpose(Ary2)
> .Cells(1, 1).CurrentRegion.Borders.LineStyle = 1
> End With
> Erase Ary1, Ary2
>End Sub
>
で、前掲例題
┌─┬─┬───┐
│A │B │C │
├─┼─┼───┤
│1 │あ│a │
├─┼─┼───┤
│2 │い│ │
├─┼─┼───┤
│3 │う│b,c │
├─┼─┼───┤
│4 │え│d │
├─┼─┼───┤
│5 │お│e,f,g │
└─┴─┴───┘
を処理してみたのですが
ワークシートSheet2に変換された表は
┌─┬─┐
│A │B │
├─┼─┤
│1 │あ│
├─┼─┤
│2 │い│
├─┼─┤
│3 │う│
├─┼─┤
│4 │え│
├─┼─┤
│5 │お│
└─┴─┘
のようになってしまい、私が望んでいた処理結果
┌─┬─┬───┐
│A │B │C │
├─┼─┼───┤
│1 │あ│a │
├─┼─┼───┤
│2 │い│ │
├─┼─┼───┤
│3 │う│b │
├─┼─┼───┤
│3 │う│c │
├─┼─┼───┤
│4 │え│d │
├─┼─┼───┤
│5 │お│e │
├─┼─┼───┤
│5 │お│f │
├─┼─┼───┤
│5 │お│g │
└─┴─┴───┘
のようにはなりませんでした。すみませんが、一度この例題でVBAコードをチェックして頂けませんか？
|
|
しん
- 04/9/4(土) 19:07 -
