EXCEL/VBA: データ数がわからない場合からFor-Nextが使えない？Do-Loopを使おう！

2017/05/27

EXCELマクロでシート内のデータから特定のデータを探すとき、For-Nextループを使って探す場合が多いです。

でも、For-Nextの欠点はデータ行数がわかっていないとなりません。例えば、For i=1 to 1000 のようにデータ数が1000行とあらかじめ分かっているから書けるんです。

じゃあ、データ数が分からない、日々データ数が変わる場合はどうすればいいんでしょうか。Excelシートをデータベースのように使う場合はその時々でデータ行数は変わりますよね。

例えば以下のような郵便番号と住所の一覧表があったとして、オレンジのところに入れた郵便番号を探すとします。データが何行あるかはわかりません。

データ数がわからないので、For-Loopだと以下のように何行目までループしていいかわかりませんね。

Sub ForNextTest()
  For i = 4 To ????  '何行目までループしていいかわからない！
    If Cells(i, 1) = Range("A2") Then
      'ここに処理を書く
      Exit For
    End If
  Next i
End Sub

なので、あり得ないと思われる行数を突っ込んで解決しようとする人がいます。たとえば、10万行はさすがにないだろうと以下のようにする例です。

Sub ForNextTest()
  For i = 4 To 100000  '10万行はさすがにないだろう！
    If Cells(i, 1) = Range("A2") Then
      'ここに処理を書く
      Exit For
    End If
  Next i
End Sub

しかし残念でした。今回の例では12万行以上もあります。上記のプログラムでは沖縄の最後の方は検索できないというバグを持っています。

[ad#top-1]

Do-Loopを使おう！

そんなときは、For-Loopではなく条件付きループのDo-Loopを使いましょう。ソースコードは以下の通りです。

Sub DoLoopTest()
  i = 4
  Do While Cells(i, 1) <> "" '(1)
    If Cells(i, 1) = Range("A2") Then
      'ここに処理を書く
      Exit Do
    End If
    i = i + 1 '(2)
  Loop
End Sub

(1)のところは、「セルが空白じゃないうちはループする」という意味です。

重要なのは(2)のところ。iをカウントアップしないと無限ループになります。このDo-Loopはこれを書き忘れる危険性があるのですが。（僕もたまにやっちゃいます。）

[ad#ad-1]

- Excel, VBA