EXCEL VBAのFor...Nextについて

質問

VBA初心者です。よく理解していませんので、質問も的を得ていないかもしれませんが、ご指導宜しくお願いいたします。　
現在、For...Ｎｅｘｔを使った表計算をしています。
A列に「す」という文字が含まれていたら、B列の「す」の行に「あ」と「い」と「え」「か」のセルの合計をだす。C列、D列・・・最終列まで計算する。
上記VBAを作成する方法を教えて下さい。

A　　B　　C　　D　　E　　　F　　G　　H　　I　　J　　K　　L
1 2 　　 3 　　 4 　 5 　　 6 　　 7 　　 8 　　 9 　　 10 　　 11 12
あ　 1 2 3 4 　 5 6 7 8 9 10 11
い 10 20 30 40 　50 60 70 80 90 100 110
う 20 30 40 50 　 60 70 80 90 100 110 120
え　40 50 　 60 　70 80 90 　10 20 120 130　30
お　50 60 　 70 　80 90 10 　20 30 130 140　40
か　60 70 　 80 　90 10 20 　30 40 140 150　50
す

私は表に１～12まで数字をインプットし下記のようなコードを考えました。
Sub 列合計()
Dim i, k, l, m, n As Long
j = 2
For i = 6 To 120
For k = 6 To 120
For l = 6 To 120
For m = 6 To 120
For n = 6 To 120

If Cells(i, 1) = "す" And Cells(k, 1) = "あ" And Cells(l, 1) = "い" And Cells(m, 1) = "え" And Cells(n, 1) = "か" Then
Do While j <= Range("A2").End(xlToRight)
Cells(i, j) = Cells(k, j) + Cells(l, j) + Cells(m, j) + Cells(n, j)
j = j + 1
Loop
Else:
End If
Next n
Next m
Next l
Next k
Next i

End Sub

この内容だとエラーが出てしまいます。

補足ですが、あいうえおかの順番はかわったり、間に他の行が入ったりします。
また今回はL列の間としましたが、もっと列が増え、最終列まで計算する方法を知りたいのですが、どうぞ宜しくお願い致します。
※ofice2013です。

回答No.2

nishi6

計算目的なら、「す」の行のB列(下は8行目に)

　=SUMPRODUCT((($A$2:$A$7="あ")+($A$2:$A$7="い")+($A$2:$A$7="え")+($A$2:$A$7="か"))*B2:B7)で計算できますが、

Forループの使い方ということで答えを書きました。

最初に要件の確認です。

＞A列に「す」という文字が含まれていたら、
　A列のあるセルが「す」だったら　と解釈しました。

＞B列の「す」の行に
　「す」があったA列のセルの右のB列のセル

＞「あ」と「い」と「え」「か」のセルの合計をだす。
　この４つの文字の場合は集計するとしました。４つが必ず1回出てくるというのは作り手に都合のいいモジュールになります。全部「あ」でも「あいう」、「ああいい」でも全部「ん」でも計算します。そのために少し冗長になっています。「あいえか」の行を決め打ちにしていません。

＞C列、D列・・・最終列まで計算する。
　最後は1行目の入力されている一番右のセルとします。

シートのコードウィンドウを使っています。（Excel2010）
まだ少しFor Nextの使い方に慣れておられないようなのでモジュールにコメントを追記しています。

Sub keisan()
　　Dim col As Integer '列カウンタ
　　Dim rw As Integer　　'行カウンタ
　　Dim writeRow As Integer '書き出す行（「す」のある行）
　　Dim lastColumn As Integer　　'最終列
　　　　lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
　　Dim Total As Double '計

　　'「す」の行を探す
　　'A列を入力が終わるまで調べます
　　rw = 2
　　While Cells(rw, 1) <> "" And writeRow = 0
　　　　If Cells(rw, 1) = "す" Then
　　　　　　writeRow = rw　　'これが「す」のある行
　　　　End If

　　　　rw = rw + 1
　　Wend

　　'「あ、い、え、か」のある行を集計
　　If writeRow > 0 Then　　　　　’「す」があれば正
　　　　'「す」の行があれば計算します
　　　　For col = 2 To lastColumn　　 '列方向
　　　　　　Total = 0　　 '計をクリア
　　　　　　For rw = 2 To writeRow - 1　　'行方向
　　　　　　　　If Cells(rw, 1) = "あ" Or _
　　　　　　　　 Cells(rw, 1) = "い" Or _
　　　　　　　　 Cells(rw, 1) = "え" Or _
　　　　　　　　 Cells(rw, 1) = "か" Then
　　　　　　　　 'A列が「あいえか」なら加算
　　　　　　　　　　Total = Total + Cells(rw, col)
　　　　　　　　End If
　　　　　　Next
　　　　　　'出力行に書き出す
　　　　　　Cells(writeRow, col) = Total
　　　　Next
　　End If
End Sub

お礼

ご教授ありがとうございます。
モジュールにコメントを記入していただき、初心者の私には、とても助かりました。
コメントを参考に勉強致します。

回答No.1

noname#203218

for ～ Next　使用が条件であれば下記はVBAの一例です。

Sub test()

Dim Myrow, Maxrow, Maxcol As Long
Dim i, j As Long
Dim Cols(1 To 4) As Long

Maxrow = Cells(Rows.Count, 1).End(xlUp).Row 'A列最大行
Maxcol = Cells(2, Columns.Count).End(xlToLeft).Column '２行目最大列

For i = 2 To Maxrow
If Cells(i, 1).Value = "す" Then Myrow = i
Next

If Myrow > 1 Then

For i = 2 To Maxrow

If Cells(i, 1).Value = "あ" Then Cols(1) = i
If Cells(i, 1).Value = "い" Then Cols(2) = i
If Cells(i, 1).Value = "え" Then Cols(3) = i
If Cells(i, 1).Value = "か" Then Cols(4) = i

Next

Else
'す　が見つからない場合はメッセージを表示し、subを抜ける。
MsgBox "「す」が見つかりません"
Exit Sub
End If

'集計値を入力するセル範囲の数値を消去
Range(Cells(Myrow, 2), Cells(Myrow, Maxcol)).ClearContents

'集計値入力セルに集計値を値として入力
For i = 2 To Maxcol
For j = 1 To 4
If Cols(j) > 1 Then
Cells(Myrow, i) = Cells(Myrow, i) + Cells(Cols(j), i)
End If
Next
Next

End Sub

検索する文字は完全一致でセルは重複しないものとしています。「す」は１つしかA列には存在しない。
他のあ、い、え、か　も同様にA列に１つしか存在しないものとしています。
部分一致のセルも集計必要ある場合は、修正必要です。

最大行、最大列を取得方法出来るようにしてあります。

VBAはサイト上にサンプルコードはいくらでもありますので、質問するよりネット検索した方が解決は早いと思います。

お礼

コード実行したら、うまく計算されました。
他の表にも応用し使用させていただきました。
ありがとうございました。
今後はサンプルコードを検索し、使いこなせるようにしていきたいです。