Excel VBA を使用して列の値を検索する

1. VBA の Find() 関数を使用して列内の文字列/値を検索する
2. VBA の Match() 関数を使用して列内の文字列/値を検索する
3. VBA のループを使用して列内の文字列/値を検索する
4. VBA のループを使用して複数の列の文字列を検索する

Excel で自動化ツールを作成するには、Instr()、CStr()、Split() などのいくつかの文字列照合関数が必要です。これらの関数は、文字列内の部分文字列を検索します。ただし、列を介して文字列/値を処理する場合、これらの関数は単一文字列関数であるため、使用できません。

この記事では、列内の文字列または値を検索するための 3つの手法について説明します。return 値は、ターゲット文字列がある行番号です。

文字列検索テクニック：

1. Find() 関数を使用する
2. 一致機能の使用
3. ループの使用

記事全体で、この仮想シートがリファレンスシートになります。

Sheet1 の値：

  |   A     | B |       C      |  D  |
1 | Apple   | 4 | Philippines  |  3  |
2 | Orange  | 3 | Mexico       |  5  |
3 | Banana  | 6 | Thailand     |  3  |
4 | Carrot  | 7 | Saudi Arabia |  7  |
5 | Grapes  | 5 | U.S.A.       |  1  |
6 | Pear    | 3 | Japan        |  0  |
7 | Citrus  | 9 | Mexico       |  3  |
8 | Pomelo  | 2 | Taiwan       |  4  |
9 |         | 3 | Singapore    |  7  |
10|         | 4 | Mexico       |  8  |

VBA の Find() 関数を使用して列内の文字列/値を検索する

構文：

Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase)

パラメーター：

以下のコードブロックは、Find() 関数を使用して検索する文字列の行を返す方法を示しています。

Function SearchStr(str As String) As Range

Dim wb As Workbook
Dim s1 As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

Set rng = s1.Columns("A:A")
Set SearchStr = rng.Find(What:=str, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

End Function

Sub BananaSearch()

If SearchStr("Banana") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Banana").Row

End If

End Sub

Sub MelonSearch()

If SearchStr("Melon") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Melon").Row

End If
End Sub

BananaSearch 出力：

Found at row: 3

MelonSearch 出力：

Not in range

VBA の Match() 関数を使用して列内の文字列/値を検索する

Find() 関数と Match() 関数の主な違いは、前者は文字列が見つかった Range オブジェクトを返し、後者は文字列が一致する位置を返すことです。

構文：

Application.Match([StringtoSearch],[RangetoSearchIn],[MatchType])

パラメーター：

備考：

[MatchType] 引数の場合、値は次のとおりです。

以下のコードブロックは、Match() 関数を使用して検索する文字列の行を返す方法を示しています。

Function SearchNum(IntToSearch As Integer) As Variant

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

If Not IsError(Application.Match(IntToSearch, s1.Columns("B:B"), 0)) Then
    SearchNum = Application.Match(IntToSearch, s1.Columns("B:B"), 0)

Else
    SearchNum = "Not Found"
End If

End Function

Sub LookForSix()

Debug.Print "A match is located at row " & SearchNum(6)

End Sub

Sub LookForZero()

Debug.Print "A match is located at row " & SearchNum(0)

End Sub

LookForSix 出力：

A match is located at row 3

LookForZero 出力：

A match is located at row Not Found

VBA のループを使用して列内の文字列/値を検索する

Find() および Match() 関数とは異なり、ループを利用すると、ユーザーはコードを実行できます。ループの利点の 1つは、範囲からすべての一致を返すことができることです。ただし、ループは指定された制限内で各セルを反復処理するため、大きなデータを処理する場合はお勧めしません。

以下のコードブロックは、ループを利用して検索する文字列の行を返す方法を示しています。

Function GetAllRows(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")
Dim i As Long
Dim lrow As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

For i = 1 To lrow
    
    If s1.Cells(i, 3) = str Then
        'Add row number in the arraylist
        coll.Add (i)
        
    End If
    
Next i

Set GetAllRows = coll

End Function

Sub FindForMexico()

Dim coll2 As Object

Set coll2 = GetAllRows("Mexico")

Dim j As Integer

For j = 0 To coll2.Count - 1

    Debug.Print "A match is found in row: " & coll2(j)

Next j

End Sub

FindforMexico 出力：

A match is found in row: 2
A match is found in row: 7
A match is found in row: 10

VBA のループを使用して複数の列の文字列を検索する

上記の 3つのコードブロックは、1つの列だけを調べるように設計されています。まれに、複数の列間、またはワークシート全体で文字列の一致を探す必要があります。ただし、必要に応じて、以下のコードブロックが役立ちます。

Function GetAllRowsFromSheet(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")

'Create row numbers(i,j)  and last row/column variable in long datatypes.
Dim i, j As Long
Dim lrow, lcol As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

lcol = s1.Cells(1, s1.Columns.Count).End(xlToLeft).Column

For j = 1 To lcol
    For i = 1 To lrow
        
        If s1.Cells(i, j) = str Then
            coll.Add ("Row :" & i & " Column : " & j)
        End If 
    Next i
Next j

Set GetAllRowsFromSheet = coll

End Function

Sub FindFor3()

Dim coll2 As Object

Set coll2 = GetAllRowsFromSheet(3)

Dim j As Integer

For j = 0 To coll2.Count - 1

Debug.Print "A match is found in " & coll2(j)

Next j

End Sub

Lookfor3 出力：

A match is found in Row :2 Column : 2
A match is found in Row :6 Column : 2
A match is found in Row :9 Column : 2
A match is found in Row :1 Column : 4
A match is found in Row :3 Column : 4
A match is found in Row :7 Column : 4