-PR-
解決済み

For Next構文について

  • 困ってます
  • 質問No.9385394
  • 閲覧数123
  • ありがとう数6
  • 気になる数0
  • 回答数3
  • コメント数0

お礼率 78% (33/42)

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

色々試行錯誤して、一番下の値になったら1の値に戻るとしてみましたが、だめでした…。(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 マシュマロ
通報する
  • 回答数3
  • 気になる
    質問をブックマークします。
    マイページでまとめて確認できます。

質問者が選んだベストアンサー

  • 回答No.2
レベル14

ベストアンサー率 28% (4404/15620)

他カテゴリのカテゴリマスター
本件(質問のやり方のコードの是正方法)は、別に勉強していただくとして、
F列でソートして、連番(直前の番号+1を振る)を振るが、F列(コード)で数字が変わるごとに1に戻して、直前の番号+1を振る作業を続ければよい。
AutoFilterのようなものを使わずともできる方法です。
じっくり、数種ある処理ロジックの適否(プログラム行数が少ないなど。少ないと誤りも少ない)を考えることが必要だと思う。
それには、ロジックのレパートリを日ごろから増やしておかないと、間に合わない。
ーー
本件データの状況、目的や最終結果などが、質問からよく伝わらないのだが
下記は参考に上げます。
標準モジュールに
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
ソート法(この方式は、色んな場面で役に立ちます)
元データ例
F1:I12
コード項目1項目2項目3
1a1 b1 c1
3a2 b2 c2
1a3 b3 c3
2a4 b4 c4
4a5 b5 c5
4a6 b6 c6
2a7 b7 c7
1a8 b8 c8
3a9b9c9
4a10b10c10
5a11b11c11
として
実行後
A1:A12
連続番号
1
2
3
1
2
1
2
1
2
3
1
F1:I12
コード項目1項目2項目3
1a1b1c1
1a3b3c3
1a8b8c8
2a4b4c4
2a7b7c7
3a2b2c2
3a9b9c9
4a5b5c5
4a6b6c6
4a10b10c10
5a11b11c11
実行後のA列がコード別連番です。
お礼コメント
osashi

お礼率 78% (33/42)

希望していた通りの処理になりました!ありがとうございます!ご提示いただたいたVBA、読む分には何となく理解できますが、これを自分で記述しようとなると中々難しいですね…(T_T)でも頑張って勉強しようと思います、ありがとうございます!!
投稿日時 - 2017-10-21 13:23:14

その他の回答 (全2件)

  • 回答No.1
レベル14

ベストアンサー率 43% (7214/16615)

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

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列に連番を1つでもセットすると、終了値が表の最後の行番号にならない」ので、値を1つでも入れれば、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
お礼コメント
osashi

お礼率 78% (33/42)

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


  • 回答No.3
レベル11

ベストアンサー率 48% (165/338)

Excel(エクセル) カテゴリマスター
VBA学習中、でしょうか?
ま、色々な考え方ができるよ、という参考までに。
ちょっと質問文の日本語が難解で正確に読み取れているか自信薄ですが、
要するに「F列の値ごとに連番を振り、A列に入力」という事でしょうか。
違ったらスルーの方向で。

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

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

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

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

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


以下、余談です。
この案件、VBAを使わずに考えたらどうなるでしょう?
実はこっちの方が簡単ですが、おなじみの関数
  A2セル:=COUNTIF($F$2:F2,F2)
       以下、必要分フィル。
COUNTIFで実現可能です。
WorksheetFunction.関数(・・・)で
大半のワークシート関数をVBA内で同様に使えます。
割と便利です、というか、エクセルなのだから使わないと損です。
否定的なご意見が多いのも事実ですが(笑)。
ですが、コレを使うのも手の一つです。
例えば(1行が長いですが)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  'コピーモード解除
こんな感じです。
数万行でも(VBAの処理は)一瞬で終わります。
コレだと「エクセルの一般機能に依存した処理」ですから、
処理速度は一番早いです。
  ※前記、WorksheetFunctionはVBA依存の処理
   且つFor~Nextで回すので、ほんのわずかに遅れます。
私ならおそらくコレを採用するかな。
否定的なご意見もあるかもしれませんけどね(笑)。
お礼コメント
osashi

お礼率 78% (33/42)

WorkSheetFunction・・・!そんなものがあるとは知らなかったです!
しかも、関数と同じ記述方法なので、結構わかりやすいですね!目から鱗です・・ありがとうございます!(^O^)
投稿日時 - 2017-10-21 13:30:25
このQ&Aのテーマ
このQ&Aで解決しましたか?
関連するQ&A
このやり方知ってる!同じこと困ったことある。経験を教えて!
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。

その他の関連するQ&A、テーマをキーワードで探す

キーワードでQ&A、テーマを検索する
-PR-

特集


開業・独立という夢を持つ人へ向けた情報満載!

ピックアップ

ページ先頭へ