For Next構文について

質問

こんにちは、For Next構文について質問させてください。
F列に０～５の数字が入ったデータがあります。（データが入っているのは
AG列までです。F列以外には数字以外に色々データが入っており、A列は空白です。）
このデータをF列０～５でオートフィルタをして、Ａ列にそれぞれの数字ごとに
連番を振りたいのですが、どう頑張っても０～５まで連番で番号が振られてしま
います。（やりたいのは、数字の０のデータが終わったら次は１のデータでまた
「１」から、１のデータが終わったら次は２のデータで「１」から番号を振るという作業です。）

色々試行錯誤して、一番下の値になったら１の値に戻るとしてみましたが、だめでした…。（If Cells(ビスケット, 3) = スパゲティ Then　和菓子 = 1の部分です。）

どなたか、どの記述が悪いのか、ご教授いただけると嬉しいです！よろしくお願いいたします！(T_T)

Dim マシュマロ As Integer
Dim 和菓子 As Integer
Dim ビスケット As Long
Dim スパゲティ As String

For マシュマロ = 0 To 5

Range("A2:AG" & Cells(1).CurrentRegion.Rows.Count).AutoFilter Field:=6, Criteria1:=マシュマロ

スパゲティ = Range("C1").End(xlDown).Value

和菓子 = 1
For ビスケット = 2 To Range("A2").End(xlDown).Row
If Cells(ビスケット, 3) <> "" Then
Cells(ビスケット, 1) = 和菓子

和菓子 = 和菓子 + 1
End If
If Cells(ビスケット, 3) = スパゲティ Then
和菓子 = 1
End If

Next
Next マシュマロ

回答No.2

imogasi · ベストアンサー

本件（質問のやり方のコードの是正方法）は、別に勉強していただくとして、
Ｆ列でソートして、連番（直前の番号＋１を振る）を振るが、Ｆ列（コード）で数字が変わるごとに１に戻して、直前の番号＋１を振る作業を続ければよい。
ＡｕｔｏＦｉｌｔｅｒのようなものを使わずともできる方法です。
じっくり、数種ある処理ロジックの適否（プログラム行数が少ないなど。少ないと誤りも少ない）を考えることが必要だと思う。
それには、ロジックのレパートリを日ごろから増やしておかないと、間に合わない。
ーー
本件データの状況、目的や最終結果などが、質問からよく伝わらないのだが
下記は参考に上げます。
標準モジュールに
Sub test01()
Range("F1").CurrentRegion.Select
Selection.Offset(1, 0).Select
Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select
'---

Selection.Sort key1:=Range("F1")
lr = Range("F10000").End(xlUp).Row
mae = Range("F2")
n = 0
For i = 2 To lr
If Cells(i, "F") = mae Then
n = n + 1
Cells(i, "A") = n
Else
n = 1
End If
Cells(i, "A") = n
mae = Cells(i, "F")
Next i
End Sub
ソート法（この方式は、色んな場面で役に立ちます）
元データ例
Ｆ１：Ｉ１２
コード 項目１ 項目２ 項目３
1 a1　 ｂ１　 ｃ１
3 a2　 ｂ２　 ｃ２
1 a3　 ｂ３　 ｃ３
2 a4　 ｂ４ 　ｃ４
4 a5　 ｂ５ 　ｃ５
4 a6　 ｂ６ 　ｃ６
2 a7　 ｂ７　 ｃ７
1 a8　 ｂ８　 ｃ８
3 a9 ｂ９ ｃ９
4 a10 ｂ１０ ｃ１０
5 a11 ｂ１１ ｃ１１
として
実行後
Ａ１：Ａ１２
連続番号
1
2
3
1
2
1
2
1
2
3
1
Ｆ１：Ｉ１２
コード 項目１ 項目２ 項目３
1 a1 ｂ１ ｃ１
1 a3 ｂ３ ｃ３
1 a8 ｂ８ ｃ８
2 a4 ｂ４ ｃ４
2 a7 ｂ７ ｃ７
3 a2 ｂ２ ｃ２
3 a9 ｂ９ ｃ９
4 a5 ｂ５ ｃ５
4 a6 ｂ６ ｃ６
4 a10 ｂ１０ ｃ１０
5 a11 ｂ１１ ｃ１１
実行後のＡ列がコード別連番です。

お礼

希望していた通りの処理になりました！ありがとうございます！ご提示いただたいたＶＢＡ、読む分には何となく理解できますが、これを自分で記述しようとなると中々難しいですね…(T_T)でも頑張って勉強しようと思います、ありがとうございます！！

回答No.3

tsubu-yuki

ＶＢＡ学習中、でしょうか？
ま、色々な考え方ができるよ、という参考までに。
ちょっと質問文の日本語が難解で正確に読み取れているか自信薄ですが、
要するに「Ｆ列の値ごとに連番を振り、Ａ列に入力」という事でしょうか。
違ったらスルーの方向で。

ご提示のコードで危険だなぁと思うのが
> For ビスケット = 2 To Range("A2").End(xlDown).Row
ここ。
今、やりたいのは「Ａ列に値を入力」なので、
> A列は空白です。
の記述の通りＡ列に現状は「何も入っていない」とすると、
エクセルが許す全行（１０４８５７６行目）まで繰り返してしまいます。
これはなんとなく処理時間的にもうまくないはず。
「Ｆ列の値を基にした連番」を振るのなら、このキーはＦ列の方が良いかと。
で、途中に空白を噛んでいたりする事故を防ぐために、
有効な最終行を「下から上に」探してやります。
つまり、
　　Cells(Rows.count, 6).End(xlUp).Row
で最終行を取る方が良いと個人的には思います。

続いて、フィルタの部分。
既に出ていますが、フィルタは「隠れている」だけで
セルとしてはワークシートに「存在している」ので、
こういう処理（条件で連番を振る）には不向きかもしれません。
条件に合わない行はスルー、の方が効率は良さそうです。

伴ってもう一つ、変数「スパゲティ」はどうやら
「フィルタで見つけた（Ｃ列における）最終行」としてお使いのようです。
コレをキーにして連番をリセットしているように見えますが、
フィルタを使わないのであればコレも不要です。
「Ｆ列の値（０～５）ごとの連番」なのですから、
変数「マシュマロ」が変わるタイミングでリセットしてやる方が解り易いです。
・・・というか、そもそもそう書いていらっしゃるようですので
そもそも不要だった、と言えますね。

そんなこんなで、私が書き変えるなら
Dim マシュマロ As Integer
Dim 和菓子 As Long
Dim ビスケット As Long
Dim 最終行 As Long

　　' 最終行取得（Ｆ列）
　　最終行 = Cells(Rows.Count, 6).End(xlUp).Row
　　For マシュマロ = 0 To 5
　　　　和菓子 = 1　' 連番リセット
　　　　For ビスケット = 2 To 最終行
　　　　　　' Ｃ列が空白じゃなく、且つ、Ｆ列が「マシュマロ」と合致したら
　　　　　　If Cells(ビスケット, 3) <> "" And Cells(ビスケット, 6) = マシュマロ Then
　　　　　　　　Cells(ビスケット, 1) = 和菓子　' 連番を入力
　　　　　　　　和菓子 = 和菓子 + 1　' 連番を更新
　　　　　　End If
　　　　Next ビスケット
　　Next マシュマロ
こんな感じでシンプルに書きたいなぁ、
・・・と思ったら、１番さんとほぼ一緒でしたね。
失礼しました。

以下、余談です。
この案件、ＶＢＡを使わずに考えたらどうなるでしょう？
実はこっちの方が簡単ですが、おなじみの関数
　　Ａ２セル：=COUNTIF($F$2:F2,F2)
　　　　　　　以下、必要分フィル。
COUNTIFで実現可能です。
WorksheetFunction.関数（・・・）で
大半のワークシート関数をＶＢＡ内で同様に使えます。
割と便利です、というか、エクセルなのだから使わないと損です。
否定的なご意見が多いのも事実ですが(笑)。
ですが、コレを使うのも手の一つです。
例えば（１行が長いですが）For～Nextを使って
　　For i = 2 To Cells(Rows.Count, 6).End(xlUp).Row
　　　　Cells(i, 1) = WorksheetFunction.CountIf(Range(Cells(2, 6), Cells(i, 6)), Cells(i, 6))
　　Next
こんな感じで書けます。
処理数が数万行の単位まで膨らむのであれば、For～Nextで回すより遥かに早く終わります。

セルに式が残ってもいいのであれば、R1C1形式を使って
　　Range("A2:A" & 最終行).FormulaR1C1 = "=COUNTIF(R2C6:RC[5],RC[5])"
こんな感じでさらにシンプルに書けます。
で、コレに「セルに式を残したくない（値だけ残したい）」のであれば
　　With Range("A2:A" & Cells(Rows.Count, 6).End(xlUp).Row)　'この範囲について
　　　　.FormulaR1C1 = "=COUNTIF(R2C6:RC[5],RC[5])"　　'式を入力
　　　　.Copy　　' コピー
　　　　.PasteSpecial Paste:=xlPasteValues　　' 数値のみ貼り付け
　　End With
　　Application.CutCopyMode = False　　'コピーモード解除
こんな感じです。
数万行でも（ＶＢＡの処理は）一瞬で終わります。
コレだと「エクセルの一般機能に依存した処理」ですから、
処理速度は一番早いです。
　　※前記、WorksheetFunctionはＶＢＡ依存の処理
　　　且つFor～Nextで回すので、ほんのわずかに遅れます。
私ならおそらくコレを採用するかな。
否定的なご意見もあるかもしれませんけどね(笑)。

お礼

WorkSheetFunction・・・！そんなものがあるとは知らなかったです！
しかも、関数と同じ記述方法なので、結構わかりやすいですね！目から鱗です・・ありがとうございます！(^O^)

回答No.1

chie65536（@chie65535）

重大な間違いがあります。

For ビスケット = 2 To Range("A2").End(xlDown).Row

のFor～Nextは「オートフィルタを無視して、常に、2、3、4、5、6、7...…と増えていく」のです。

つまり「オートフィルタ関係なしに、F列の値がどうなってようが関係なしに、C列に何か入っている行に、上から順に連番を振っていく」と言う動作になってしまっています。

ですので、オートフィルタを設定しても無駄です。オートフィルタは外しましょう。

あと、

For ビスケット = 2 To Range("A2").End(xlDown).Row

のForは「A列に連番を１つでもセットすると、終了値が表の最後の行番号にならない」ので、値を１つでも入れれば、Forループが途中で止まります。

このFor文が「2行目から最終行まで」の意味であるならば、F列の一番最後の行を「最終行」としてマクロを組む必要があります。

F列が「途中に空白セルが無い状態でミッチリと0～5のデータが埋まっている状態」なら
For ビスケット = 2 To Range("F2").End(xlDown).Row
でも構いませんが、途中に空白セルがあったりすると、やはり「最終行の手前」で止まってしまいます。

「2行目から最終行まで」をループする場合は
For ビスケット = 2 To Cells(Rows.Count, 6).End(xlUp).Row
でループします。

上記を踏まえて修正すると、以下のようになります。

Sub Macro1()
Dim マシュマロ As Integer
Dim 和菓子 As Integer
Dim ビスケット As Long

'もしオートフィルタが設定してあったら
If ActiveSheet.AutoFilterMode Then
'オートフィルタを解除する
Range("A:AG").AutoFilter
End If

For マシュマロ = 0 To 5

和菓子 = 1

'2行目～F列の最終行までループ
For ビスケット = 2 To Cells(Rows.Count, 6).End(xlUp).Row

'F列がマシュマロと一致し、かつ、C列に何かあれば
If Cells(ビスケット, 6) = マシュマロ And Cells(ビスケット, 3) <> "" Then
Cells(ビスケット, 1) = 和菓子

和菓子 = 和菓子 + 1
End If

Next ビスケット
Next マシュマロ
End Sub

お礼

chie65535さんご回答いただいてありがとうございます！確かに、手動でやってみてもオートフィルタをかけたまま配番してもうまくいきません！(>_<)データがみっちり詰まっているので、End(xlDown)でもいいのかなぁ～と思ったのですが、空白ができた場合も考えてEnd(xlUp)のほうが確かによいかもしれないですね！(^O^)