表にデータを転記する時によく使うコード。
表の中から特定の文字列を探してきて、その隣のセルに転記
とか。
例えば日々のデータを転記する時に、5日のデータだったら5日の日付の行に転記したい。
ということがよくあります。
もちろん表の位置が移動しない前提で「5日は8行目にあるから～」と数字で指定してしまってもいいのだけれど、使っているうちに行を挿入したりしたくなることもよくあるわけで。そうすると位置がずれていきます。
というわけで5日という文字列を探して、見つかった行に転記する
というコードが必要になってくるわけです。
それでは解説いってみましょ～！
どういう時に使う？の具体例
まず、「文字列を探すのってどういうシチュエーション？」というお話から。
こんな感じの表とデータの入ったCSVファイルがあったとします。
data.csvには月、日、内容が順不同に記録されています。
その内容を左の表に転記するVBAを作りたいです。
さあどうしましょう？
方法を日本語で書くと、
① data.csvの2列目から日の情報を取得して、左の表の中から当てはまる行を探す
②見つかったらその行のC列にdata.csvの3列目の情報を転記する
という感じでしょうか。
サンプルコード その１
では、先ほど日本語で書いた文章をコードにしてみましょう。
説明を簡単にするために、data.csvのデータが表と同じファイルの同じシート上にあるとしてVBAを書きます。（data.csvを開いたりとかするとコードがややこしくなって本質を見失うので）
このような状態で、先ほど説明した日本語をコードに直すとこんな感じ。
Sub Sample1()
Dim i As Long, j As Long
Dim D As Long, Memo As String
For i = 4 To 10
'data.csvの情報をそれぞれ D(日)、Memoに代入しておく
D = Cells(i, 7)
Memo = Cells(i, 8)
'表の日と合致する行を探してmemoの内容を転記する
For j = 4 To 30
If Cells(j, 2) = D Then
Cells(j, 3) = Memo
Exit For
End If
Next j
Next i
End Sub
こんな感じで転記をするときに、特定の文字列を探すコードをよく使います。
よく使うのでfunctionとして部品にしておいて、使いたいときに呼び出すと便利ですね。この話は後述します。
サンプルコード その２
VBAを使い始めていた頃は上記のような感じで、For文を使って合致するものを探すということをしていました。
もちろんこれでも良いです。1行ずつチェックをするという地道な作業だろうが、見た目がスマートじゃなかろうが、結果が正しければ万事OKなんです！
…ともいえないんですよね。
今回は4行目から30行目までしか探していません。なので上のコードでも問題はありません。
では、探す範囲が1000行だったら？10000行だったら？
もちろん人間が目で探すのに比べたらプログラムが探す時間なんて微々たるものです。とはいえ塵も積もればということもあるので、できるだけ時短を試みる姿勢が大切です。
というわけで代案のコードがこちら。
Sub Sample2()
Dim i As Long
Dim D As Long, Memo As String
Dim buf As Long
For i = 4 To 10
'data.csvの情報をそれぞれ D(日)、Memoに代入しておく
D = Cells(i, 7)
Memo = Cells(i, 8)
'表の日と合致する行を探してmemoの内容を転記する
buf = ReturnCellNum(D)
Cells(buf, 3) = Memo
Next i
End Sub
Function ReturnCellNum(Name As Long) As Long
Dim FoundCell As Variant
Set FoundCell = Range("B1:B35").Find(Name)
If FoundCell Is Nothing Then
MsgBox "見つかりませんでした"
Else
ReturnCellNum = FoundCell.Row
End If
End Functionコードの長さが長くなったように見えますが、時間は短縮されているはず！
『表の日と合致する行を探してmemoの内容を転記する』の部分がFor文ではなくなってます。100回ループとか1000回ループとかが無くなるわけですね。
どうやって合致する日を探しているかというと、findというメソッドを使っています。
Range(範囲).Find（探したい文字)
という書き方になります。
見つかったセルをFoundCellという変数に入れておいて、そのセルの行番号をFoundCell.Rowで抽出する、という流れです。
このFunctionの部分をコピペしてReternCellNumで呼び出せば、簡単に文字列を探して行番号を取得することができます。
このように、よく使うものは部品にしておくのがオススメです。
※今回は探すものが数字だったのでDとNameの型がlongになっていますが、文字列を探す場合はstirngに変更する必要があります。
汎用性を考えるのであれば、NameはVariantにしておくのもアリです。
YouTubeにてコードを１行ずつ実行しながら解説をしています。
こちらも併せてご覧いただくと理解がしやすいと思います。
コメント