エクセルVBAのヒントをください

質問

エクセルのある列に、文字列が入っているとします。
たとえば、Aであったり、Cであったり、A,C,Fとカンマ区切りで複数の文字列（個数は不定）
が入っていたりします。
このとき、一行に一つの文字列しか含まないように、カンマで区切られた文字列を展開し、
複数行に分けて格納したいと思っています。

たとえば、一番上の行のセルにA、二番目の行に「C,D,A」、三番目の行にB、四番目の行に
「E,A」と入っていたら、各行に一文字列のみ入るように、上からA、Ｃ、D、A、Ｂ、E、Aという行を
作りたいと思います。各セルに含まれる文字列の個数は不定で、規則性はありません。

基本的に、
(1)各セルに含まれているカンマの数を調べる
(2)カンマの数だけ次の処理を繰り返す
　・行挿入
　　・最初のカンマの位置を調べる
　　・先頭からカンマまでを切り取る
　　・上記の値をセルに入力
　・行挿入
　　・2番目のカンマの位置を調べる
　　・1番目のカンマの位置＋１から2番目のカンマの位置まで切り取る
　　・上記の値をセルに入力
上記をカンマの数だけ繰り返す。

といった具合にしていくのだと思いますが、このような処理をするのに
はどの程度のVBAのスキルが必要ですか？

上記のヒントや、参考になるサイト、書籍があればご教授ください。

回答No.3

end-u · ベストアンサー

>とりあえずのことはできました。
なら別に良いですが
>..Worksheets("Sheet1").UsedRange.Rows.Count
Sheet1のA:B列以外にデータがあればUsedRange.Rows.Countは多くなったりする可能性がありますから危ういですね。
無駄にLoopする事になります。

>For i = 0 To ingYCnt
>　　y = i + 1
iをなぜ0から始めるのか、かつ y = i + 1 で毎回 +1 して i はLoopの中で使ってません。
1から始めて y の代わりに i を使えば良いです。

行挿入してコピーして貼り付け、よりも
上から順番に値をセットしていったほうが良いでしょう。
『例えば』
不要になった y を 値セット先の行位置用にカウントアップさせて
For i = 1 To ingYCnt
　　tmp = Split(Worksheets("sheet1").Cells(i, 2).Value, ",")
　　For k = 0 To UBound(tmp)
　　　　y = y + 1
　　　　Worksheets("sheet2").Cells(y, 1).Value = Worksheets("sheet1").Cells(i, 1).Value
　　　　Worksheets("sheet2").Cells(y, 2).Value = tmp(k)
　　Next k
Next i
とか書けます。

効率を考えれば tmp を Transpose してまとめて書き込んだほうが良いですし
Worksheets("sheet2")などをWithで括ったり変数に入れたりして、
など、改善の余地はありますが、
まずは理解できるところで止めておいても良いかもしれませんね。

お礼

大変ご丁寧なアドバイスありがとうございました。

この記事を書いた後、Forループを逆順にして、STEP　-1することで、昇順降順もなんとかなりました。

VBAは初めてで、アドバイスいただいた内容も、正確に理解できているとは言えませんが、とても感謝しています。

今後とも、よろしくお願いいたします。

回答No.2

end-u

>といった具合にしていくのだと思いますが、このような処理をするのに
>はどの程度のVBAのスキルが必要ですか？

・処理範囲を調べたり指定したりするため
　CurrentRegionプロパティやEndプロパティやOffsetプロパティ
　場合によってはFindメソッドやWorksheetFunction.CountA関数とか
・不定数のデータを順次処理するため
　Len関数やCountプロパティで個数を取得できるとか
　Do-Loop、For-Nextステートメントを使ってLoop処理できるとか。
・カンマ数を調べたり１文字切り取るため
　Len関数やMid関数、場合によってはReplace関数
・行挿入するため
　Insertメソッド
・値をセルに入力するため
　Valueプロパティを使って値を入力できること
・その他
　変数について理解しておくこと

必須ではありませんが、だいたい上記のような事を知って使えるスキルが必要だと思われます。

ただ、最終の処理結果を得る為の手法はいくつもあります。
手法の違いによって必要なスキルは変わってきます。
..というよりスキルがあがる事によって手法の選択肢が広がっていく感じです。
スキルに合わせて工夫して処理を実現させる考え方もあるかと思います。
極端な話、Excelの機能をよく知っていればマクロの自動記録を録って
若干の修正を加えるだけで可能だったりします。
提示の例だと
（作業エリアを十分に使えるという前提で）
[データ]-[区切り位置]（TextToColumnsメソッド）
[形式を選択して貼り付け]-[行列を入れ替える]（PasteSpeciaメソッドTranspose引数）
この機能を中心に考えると、データ量によっては処理可能です。
可変範囲や不定数データに対する処理や、
Loop処理方法については知っておいたほうが効率があがりますが。

少し前ですが似たような案件。
http://okwave.jp/qa/q7133888.html?order=asc
セル内改行文字vbLfと","を置き換えて考えてみると良いです。

お礼

以下のようなスクリプトにしたら、まだ、昇順・降順は逆ですが、とりあえずのことはできました。
ありがとうございました。

Sub Macro1()
'
' Macro1 Macro
'
Dim ingYCnt As Long
Dim tmp As Variant
Dim i As Long
Dim k As Long
Dim y As Long

ingYCnt = Worksheets("Sheet1").UsedRange.Rows.Count

For i = 0 To ingYCnt
y = i + 1

tmp = Split(Worksheets("sheet1").Cells(y, 2), ",")
For k = 0 To UBound(tmp)
MsgBox tmp(k)
Application.CutCopyMode = False

Worksheets("sheet2").Range("1:1").Insert

Worksheets("sheet1").Rows(y).Copy
Worksheets("sheet2").Range("1:1").PasteSpecial

Worksheets("sheet2").Range("B1") = tmp(k)
Next k
Next i
End Sub

回答No.1

nag0720

カンマ区切りの文字列を分離する関数として、Split関数があります。

お礼

ありがとうございます。
配列に入れてくれるので、便利ですね。
使ってみます。

補足

以下のようなスクリプトを作成しました。

sheet1のセルA1に”ああああ”、セルB1に”aaa,bbb,ccc"
sheet1のセルA2に”いいいい”、セルB2に”ddd”
という文字列が入っています。

このスクリプトを実行すると、sheet2のB1からB4にかけて、

dddd　
ccc
bbb
aaa

という文字列が入り、一応の達成ができました。
ありがとうございました。

しかし、sheet2に

いいいい　ddd
ああああ　ccc
ああああ　bbb
ああああ　aaa

と表示させようと思ったら、エラーになります。

何か、良い方法はありますか？

Sub Macro1()
'
' Macro1 Macro
'
Dim ingYCnt As Long
Dim tmp As Variant
Dim i As Long
Dim k As Long
Dim y As Long

ingYCnt = Worksheets("Sheet1").UsedRange.Rows.Count

For i = 0 To ingYCnt
y = i + 1

tmp = Split(Worksheets("sheet1").Cells(y, 2), ",")
For k = 0 To UBound(tmp)
MsgBox tmp(k)
Application.CutCopyMode = False

Worksheets("sheet2").Range("1:1").Insert

' Worksheets("sheet1").Range("y:y").Copy
' Worksheets("sheet2").Range("1:1").Insert

Worksheets("sheet2").Range("B1") = tmp(k)
Next k

Next i
End Sub