VBA でよく使うメソッド 7

「VBAトップへ」
　　　7　VBA でよく使うメソッド
　　検索
　　連続検索
　　最終行と最終列の取得
　　ソート
　　オートフィルタ



検索

シート内の文字列の検索にはFindメソッドを使用します。引数にて検索条件を指定します。

メソッド引数定数内容
FindWhat　検索するデータを指定
After　検索を開始するセルを指定。省略すると左上端から開始する
LookInxlFormulas検索対象の種類を指定。数式
xlValues値
xlComentsコメント文
LookAtxlPart一部が一致するセルを検索
xlWhole全てが一致するセルを検索
SearchOrderxlByRows検索の方向を指定。列
xlByColumns検索の方向を指定。行
SearchDirectionxlNext検索の順序を指定。順方向(規定値)
xlPrevious検索の順序を指定。逆方向
MatchCaseTrue大文字と小文字を区別する。
False区別しない(規定値)
MatchByteTrue半角と全角を区別する
False区別しない(規定値)

下記の例では "Sheet1"に文字列"りんご"が存在するか検索し、存在した場合。その位置を表示します。 


Sub Search()
    Dim lngYLine As Long
    Dim intXLine As Integer
    Dim Obj As Object
    
    Set Obj = Worksheets("Sheet1").Cells.Find("りんご")
    If Obj Is Nothing Then
        MsgBox "りんごは見つかりませんでした。"
    Else
        lngYLine = Worksheets("Sheet1").Cells.Find("りんご").Row
        intXLine = Worksheets("Sheet1").Cells.Find("りんご").Column
        MsgBox "りんごは、" + CStr(lngYLine) + "行目の" _
                + CStr(intXLine) + "列目にあります"
    End If
End Sub


下記の例では "Sheet1"に文字列"りんご"が存在するか検索し、全てが一致する場合にその位置を表示します。 


Sub Search()
    Dim lngYLine As Long
    Dim intXLine As Integer
    Dim Obj As Object
    
    Set Obj = Worksheets("Sheet1").Cells.Find("りんご", LookAt:=xlWhole)
    If Obj Is Nothing Then
        MsgBox "りんごは見つかりませんでした。"
    Else
        lngYLine = Worksheets("Sheet1").Cells.Find("りんご", LookAt:=xlWhole).Row
        intXLine = Worksheets("Sheet1").Cells.Find("りんご", LookAt:=xlWhole).Column
        MsgBox "りんごは、" + CStr(lngYLine) + "行目の" _
                + CStr(intXLine) + "列目にあります"
    End If
End Sub


Sub test()
    Dim intAge As Integer
    intAge = InputBox("あなたの年齢を入力してください。")
      
    If intAge > 19 Then
        MsgBox "成人です。"
    Else
        MsgBox "未成年です。"
    End If
End Sub

条件式では論理演算子を使用することができます。

演算子 意味
条件式1 And 条件式2 条件式1と条件式2の両方を満たした場合True
条件式1 Or 条件式2 条件式1か条件式2のいずれか1つを満たした場合True
Not 条件式 条件式を満たさない場合True

以下の例では論理演算子を使用して処理を分岐させています。 


Sub test()
    Dim intScore As Integer
    intScore = InputBox("得点を入力してください")
    
    If 79 < intScore And intScore <= 100 Then
        MsgBox "優です"
    ElseIf 69 < intScore And intScore < 80 Then
        MsgBox "良です"
    ElseIf 59 < intScore And intScore < 70 Then
        MsgBox "可です"
    ElseIf 0 < intScore And intScore < 60 Then
        MsgBox "不可です"
    Else
        MsgBox "得点は 0〜100の数字で入力してください"
    End If
    
End Sub

先頭へ戻る

連続検索

シート内の文字列の検索にはFindメソッドを使用します。下記の例では "Sheet1"に文字列"りんご"が存在するか検索し、存在した場合。その位置を表示します。 


Sub Search()
    Dim lngYLine As Long
    Dim intXLine As Integer
    Dim objFind As Object
    Dim strAddress As String
 
    Set objFind = Worksheets("Sheet1").Cells.Find("りんご")
    If Not objFind Is Nothing Then
        strAddress = objFind.Address
        Do While Not objFind Is Nothing
            lngYLine = objFind.Cells.Row
            intXLine = objFind.Cells.Column
            MsgBox "りんごは、" + CStr(lngYLine) + "行目の"  _
                          + CStr(intXLine) + "列目にあります"
            Set objFind = Cells.FindNext(objFind)
            If strAddress = objFind.Address Then
                Exit Do
            End If
        Loop
    Else
        MsgBox "見つかりませんでした"
    End If
End Sub

先頭へ戻る

最終行と最終列の取得

使用済みセルの最終行と最終列の取得にはWorksheetオブジェクトの値があるすべてのセルを範囲とする UsedRange プロパティを使用します 


Sub CellCnt()
    Dim lngYCnt As Long
    Dim intXCnt As Integer
    
    lngYCnt = Worksheets("Sheet1").UsedRange.Rows.Count
    intXCnt = Worksheets("Sheet1").UsedRange.Columns.Count
    MsgBox "最終行は" & intYCnt & "行、" &  _
                            "最終列は" & lngXCnt & "列です"
End Sub

先頭へ戻る

　 　

ソート

セル範囲のソートにはSortメソッドを使用します。引数により昇順、降順などを指定できます。

メソッド引数定数内容
Sort
Key1文字列、Rangeオブジェクトを指定します。
Order1xlAscending昇順に並び替える
xlDescending 降順に並び替える
Key22番目に優先される文字列,Rangeオブジェクトを指定します
Order2xlAscending昇順に並び替える
xlDescending 降順に並び替える
Key33番目に優先される文字列,Rangeオブジェクトを指定します
Order3xlAscending昇順に並び替える
xlDescending 降順に並び替える
HeaderxlGuess先頭行をタイトル行か自動判定する
xlNo先頭行をタイトル行と見なさない
xlYes先頭行をタイトル行と見なす
OrderCustom
MatchCaseTRUE大文字・小文字を区別する
FALSE大文字・小文字を区別しない
OrientationxlSortRows 上から下へ(行の並び替え)
xlSortColumn左から右へ(列の並び替え)
SortMethodxlPinYin
xlStroke 


'セル範囲"A1:B100"を2列目をキーにして昇順にソート
Sub SortTest()
      Worksheets("Sheet1").Activate
      Worksheets("Sheet1").Range(Cells(1, 1), Cells(100, 2)) _ 
               .Sort Key1:=Worksheets("Sheet1").Cells(1, 2),order1:=xlAscending
End Sub

'セル範囲"A1:B100"を2列目をキーにして降順ににソート
Sub SortTest()
      Worksheets("Sheet1").Activate
      Worksheets("Sheet1").Range(Cells(1, 1), Cells(100, 2)) _ 
               .Sort Key1:=Worksheets("Sheet1").Cells(1, 2),order1:=xlDescending
End Sub

'セル範囲"A1:B100"を2列目をキーにして昇順にソート
Sub SortTest()
      Worksheets("Sheet1").Activate
      Worksheets("Sheet1").Range("A1:B100") _
              .Sort Key1:=Range("B1"),order1:=xlAscending
End Sub

'セル範囲"A1:B100"を2列目をキーにして降順ににソート
Sub SortTest()
      Worksheets("Sheet1").Activate
      Worksheets("Sheet1").Range("A1:B100") _
             .Sort Key1:=Range("B1"),order1:=xlDescending
End Sub


以下の例ではキーを複数指定してセル範囲をソートしています。


'セル範囲"A1:C100"を1列目は降順、2列目は昇順、3列目は降順にソート
Sub SortTest()
     Worksheets("Sheet1").Range(Cells(1, 1), Cells(300, 3)) _
        .Sort Key1:=Worksheets("Sheet1").Cells(1, 1), order1:=xlDescending, _
              Key2:=Worksheets("Sheet1").Cells(1, 2), order2:=xlAscending, _
              Key3:=Worksheets("Sheet1").Cells(1, 3), order3:=xlDescending
End Sub

'セル範囲"A1:C100"を1列目は降順、2列目は昇順、3列目は降順にソート
Sub SortTest()
      Worksheets("Sheet1").Activate
      Worksheets("Sheet1").Range("A1:C100") _
             .Sort Key1:=Range("A1"), order1:=xlDescending, _
                   Key2:=Range("B1"), order2:=xlAscending, _
                   Key3:=Range("C1"), order3:=xlDescending
End Sub


Do
    条件式を満たすまで繰り返す処理
Loop While 条件式

下記の例では、まず初期値として変数 i に 10 が代入しておき、セルに値を代入し、次に i の値を1つ加算し、 i が11以下かどうかを評価します。つまりWhile 条件式を評価する前に処理を 1回実行しています。i　の値は11なので 繰り返す処理を実行せずに終了します。 


Sub test()
'セルのA1〜A10に1〜10を入力する
    i = 10
    Do
        Worksheets("Sheet1").Cells(i, 1).Value = i
        i = i + 1
    Loop While i < 11
End Sub


>>Untilを使用する場合

Until〜には"Until 条件式"の形式で条件を指定します。条件式がtrueの間、処理が実行されます。 従って、通常は処理の中でUntil文の条件式がfalseになるようにしたり、 繰り返しを中断するような文を記述します。そうでないとUntil文は処理を永遠にし続けてしまうからです。

"Until 条件式"の記述には2つの形式があり、記述位置により処理内容が異なってきます。 Untilの位置を前にした場合、条件を満たしているのかを判断してから 繰り返す処理を実行します。 


Do Until 条件式
    条件式を満たすまで繰り返す処理
Loop

下記の例では、まず初期値として変数 i に 1 が代入しておき、次に i が11以下かどうかを評価し、セルに値を代入し、次に i の値を1つ加算し、 もう一度 i が11かどうかを評価し、セルに値を代入します。この処理を i が11になるまで繰り返します。 


Sub test()
'セルのA1〜A10に1〜10を入力する
    i = 1
    Do Until i = 11
        Worksheets("Sheet1").Cells(i, 1).Value = i
        i = i + 1
    Loop
End Sub

これに対してUntilの位置を後ろにした場合、繰り返す処理を実行してから 条件式を満たしているか判断します。つまりどのような条件でも1回は繰り返す処理 を実行します。 


Do
    条件式を満たすまで繰り返す処理
Loop Until 条件式

下記の例では、まず初期値として変数 i に 10 が代入しておき、セルに値を代入し、次に i の値を1つ加算し、 i が11かどうかを評価します。つまりUntil 条件式を評価する前に処理を 1回実行しています。i　の値は11なので 繰り返す処理を実行せずに終了します。 


Sub test()
'セルのA1〜A10に1〜10を入力する
    i = 10
    Do
        Worksheets("Sheet1").Cells(i, 1).Value = i
        i = i + 1
    Loop Until i = 11
End Sub

先頭へ戻る

　 　

オートフィルタ

データの抽出にはAutoFilerメソッドを使用します。引数にて抽出条件を指定します。

メソッド引数定数内容
AutoFilter
Field　フィルタの対象となるフィールド番号(リストの左側から数えた番号)を指定します
Criteria1　抽出条件となる文字列を指定します。
Criteria2　2番目の抽出条件となる文字列を指定します。Criteria1とOperator と組み合わせて、複合抽出条件を指定します
VisibleDropDowntrueドロップダウン矢印を表示
Falseドロップダウン矢印を非表示
OperatorxlAndCriteria1かつCriteria2
xlOrCriteria1またはCriteria2
xlTop10Items上位10番
xlBottom10Items下位10番
xlTop10Percent上位10%
xlBottom10Percent下位10%


'フィールド番号1(セルA列)の中から"6"に一致するデータを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Cells(1, 1).AutoFilter Field:=1, Criteria1:="6"
End Sub

'フィールド番号1(セルA列)の中から"6"に一致するデータを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:="6"
End Sub

'フィールド番号2(セルB列)の中から"A"に一致するデータを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Cells(1, 1).AutoFilter Field:=2, Criteria1:="A"
End Sub

'フィールド番号2(セルB列)の中から"A"に一致するデータを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="A"
End Sub

'フィールド番号2(セルC列)の中から40以上60未満の条件に一致するデータを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Cells(1, 1).AutoFilter Field:=3, Criteria1:="<60", _
                                         Operator:=xlAnd, Criteria2:=">=40"
End Sub

'フィールド番号3(セルC列)の中から40以上60未満の条件に一致するデータを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Range("A1").AutoFilter Field:=3, Criteria1:="<60", _
                                         Operator:=xlAnd, Criteria2:=">=40"
End Sub

'フィールド番号3(セルC列)の中から上位10番のものを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Range("A1").AutoFilter Field:=3, Operator:=xlTop10Items
End Sub

'フィールド番号3(セルC列)の中から上位10番のものを抽出
Sub AutoFilter()
    Worksheets("Sheet1").Range("A1").AutoFilter Field:=3, Operator:=xlTop10Items
End Sub

　　　先頭へ戻る　　　

「中級・特別編トップへ」