【Excel VBA】カンマ毎にデータを区切る

質問

はじめて質問をさせて頂きます。
Excel VBAは初心者です。

仕事で必要なため、本を読みながら考えておりましたが、
手も足も出ない状態です。

【質問内容】
Excel VBAにて、ExcelファイルのA列のみに格納されているデータを
カンマ「,」の位置毎に区切ります。
※格納されているデータ例：A,B,C,D,E

Excelの区切り位置にて、カンマで区切る処理と同じです。
A1から処理を開始し、A列のデータがなくなるまで
上記の処理を行います。

下記のプログラムを実行すると、1行目は正常にカンマ毎に
区切られますが、2行目以降はカンマ毎に区切られず、
そのままの状態です。

どこに問題があり、どのように修正すれば良いのでしょうか、
ご教示頂けると助かります。宜しくお願いします。

【プログラム】
Sub カンマ毎に区切る()
Dim mydata As String
Dim myArray() As String
Dim i, j As Integer
j = 0

Do While Cells(j + 1, "A").Value <> ""
mydata = Cells(j + 1, 1)
myArray() = Split(mydata, ",")
For i = 0 To UBound(myArray)
Cells(1, i + 1).Value = myArray(i)
Next
j = j + 1
Loop
End Sub

回答No.1

end-u · ベストアンサー

>どこに問題があり、どのように修正すれば良いのでしょうか、
>Cells(1, i + 1).Value = myArray(i)
ここです。Cells(1, i + 1)で常に1行目を指定していますから。

変数jを使えば良いです。
>j = 0
ここを 1 から始めて
>Do While Cells(j + 1, "A").Value <> ""
>　　mydata = Cells(j + 1, 1)
ここも j + 1 ではなく j に修正。

ついでに。
j = 1
Do While Cells(j, "A").Value <> ""
　　mydata = Cells(j, 1).Value
　　myArray() = Split(mydata, ",")
　　Cells(j, 1).Resize(, UBound(myArray) + 1).Value = myArray
　　j = j + 1
Loop
配列myArrayのサイズに合わせて書き込み先をResizeすればLoop不要です。

それよりも
『Excelの区切り位置にて、カンマで区切る処理と同じです。』
なのでTextToColumnsメソッドを使えば良いと思いますが。
[データ]-[区切り位置]をマクロ記録してみてください。

お礼

end-uさんのアドバイスにより、無事に解決しました。
早急で的確なアドバイス、有難う御座います。

回答No.4

watabe007

参考に
Dim c As Range, myArray As Variant
For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
　　myArray = Split(c.Value, ",")
　　c.Resize(, UBound(myArray) + 1).Value = myArray
Next

お礼

多くの方のアドバイスにより、無事に解決致しました。
watabe007さんのプログラムを試してみます。
アドバイス有難う御座います。

回答No.3

nda23

犯人はコレ→Cells(1, i + 1).Value = myArray(i)
処理するデータは行（ｊ）で変わるのに、結果は常に１行目に出力されます。

Dim mydata As String
Dim myArray As Variant '★Variantに変更
Dim myElement As Variant '★新規
Dim mySheet As WorkSheet '★新規
Dim i As Long, j As Long '★特に理由が無ければlongの方が良い
j = 0
'★明示的にワークシートを指定すべき
Set mySheet = ThisWorkbook.WorkSheets(1)
Do
　　　　j = j + 1
　　　　mydata = mySheet.Cells(j, 1)
　　　　If mydata = "" Then Exit Do
　　　　myArray = Split(mydata, ",") '★()は不要
　　　　i = 0
　　　　For Each myElement In myArray
　　　　　　　　i = i + 1
　　　　　　　　Cells(j, i).Value = myElement
　　　　Loop
Loop

お礼

nda23さんのアドバイス、判りやすくて参考になりました。
無事に解決致しました、有難う御座います。

回答No.2

n-jun

Ａ列全体を選択して、
「データ」－「区切り位置」でカンマで区切ればいいのでは？

お礼

n-junさんのアドバイスはごもっともです。
実は諸事情により、VBAでカンマ区切りをする必要がありましたが、
多くの方のアドバイスにより、無事にVBAでカンマ区切りができました。
有難う御座います。