Excel VBA(エクセル マクロ) 関数集 検索、置換

Vlookup、Match、Find、Replace、ISERROR

Excel VBAでの検索、置換方法

・Vlookup
Excel標準機能のVlookup関数をVBAで使用できる。

Vlookupの前に「Application.WorksheetFunction.」をつければ、Excel標準機能と同じように使用できる。
検索された行の他の列の値を取得できる。

[VLOOKUPの標準関数の使用方法]
=VLookup(E2, A1:C5, 2, 0)
A1からC5の範囲でE2の値があるか検索し、E2のある行でA列から2番目の値を抽出する。
なければN/Aとエラーを返す。

[VBAでの使用方法]
Cells(line1, 16).Valueの値をWorksheets("データ").Range("E2:AE10000")から検索し、左端から8行目の値をCells(line1, 11).Valueへ代入する。

Cells(line1, 11).Value = Application.WorksheetFunction.VLookup(Cells(line1, 16).Value, Worksheets("データ").Range("E2:AE10000"), 8, 0)


Vlookupの注意点として、

@検索値は検索範囲の左端に無いといけない(E2の列に無いといけない)
A検索値と検索元のデータ型を一致させないといけない。
BVBAで使用する場合、検索値が無いとエラーとなる。
On Error Resume Next
Application.WorksheetFunction.VLookup(・・・)
On Error GoTo 0
をつけて、エラー回避を行い様子を見よう。

等の注意点があり、検索されないときは上記3点を見直してみよう。

・Match
Excel標準機能のMacth関数をVBAで使用できる。

Macthの前に「Application.WorksheetFunction.」をつければ、Excel標準機能と同じように使用できる。
検索された行の行番号を取得できる。

[MATCHの標準関数の使用方法]
=MATCH(E2, A1:A5, 0)
A1からA5の範囲でE2の値があるか検索し、E2のある行番号を抽出する。
なければN/Aとエラーを返す。
MATCHは一列しか検索できないようだ。少ない範囲なら二列検索できたが、検索範囲を多くしたらエラーになった。
はまったので注意しよう。

ISERROR関数と組み合わせると効果的。検索値があるかどうかの判定ができる。
=ISERROR(Macth(E2, A1:A5, 0)
E2の値がA1:C5の中にあればエラーを返さないのでFALSE(0)を返す。なければエラーを返すのでTRUE(1)を返す。IF文で検索値があった場合は・・・と条件分岐に威力を発揮する。

[VBAでの使用方法]
Cells(line1, 16).Valueの値をWorksheets("データ").Range("E2:AE10000")から検索し、一致していれえば行番号をCells(line1, 11).Valueへ代入する。

Cells(line1, 11).Value = Application.WorksheetFunction.Match(Cells(line1, 16).Value, Worksheets("データ").Range("E2:E10000"), 0)

0の部分を1にすれば直近上位の値を検索して行番号を取得。
0の部分を-1にすれば直近下位の値を検索して行番号を取得。

・Find
Excel標準機能の【編集】→【検索】を行う。

りんごを完全一致(xlWhole)で検索し、セル番地を表示する。Set関数を用いて、Range(セル番地)の形式で値を取得する。部分一致はxlPartで検索。

Dim myRng As Range
Set myRng = Range("1:65536").Find(what:="りんご" , LookAt:="xlWhole")
Msgbox myRng.Address

・Replace
Excel標準機能の【編集】→【置換】を行う。

りんごをリンゴに置換する。

Selection.Replace what:="りんご" , replacement:="リンゴ"


忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP