【VBA】Excelマクロで特定の文字を含むか判定する方法|InStr

VBA
スポンサーリンク

VBAなら、セルのデータに特定の文字が入っているか調べられます。

必要な項目の位置を見つけるのに便利ですよ。

ある項目のセルをコピーしたいんだけど、毎回列が違う

・この文字が含まれるときだけ処理したいのに…

・コピペを完全自動化したい

こんな悩みを解決する内容です。

特定の文字が含まれるか判別する方法

InStr

InStrを使えば、特定の文字が含まれるか判別することができます。

Sub test()

Target = "123456789"
pos = InStr(Target, "5")
MsgBox pos

End Sub
InStr(“文字列”, “検索文字”)

文字列”123456789″の左から何番目に”5″があるかを調べています。

InStrのコード解説

なので、実行すると数字の5が表示されるはずです。

別の例も紹介します。↓

Sub test()

Target = "hello world"
pos = InStr(Target, "world")
MsgBox pos

End Sub

このコードでは、”hello world”の中で”world”が左から何番目かを確認しています。

InStrで単語を検索する解説図

“hello”と半角スペースで6文字なので、7文字目からが”world”ですね。

一文字目の位置が出力されるので注意しましょう!

検索文字が複数ある場合

検索文字が複数含まれる場合は先に見つかった位置を返します。

Sub test()

Target = "hello world"
pos = InStr(Target, "l")
MsgBox pos

End Sub
lが複数ある単語で検索した例

この場合、”l”は”hello”にも”world”にも含まれますが、実行すると3だけが表示されます。

最初に”l”が含まれているのが”hello”の3文字目だからです。

検索文字を含まない場合

検索文字が含まれない場合は0になります。

Sub test()

Target = "hello world"
pos = InStr(Target, "good")
MsgBox pos

End Sub

“good”は含まれていないので、実行してみると0が表示されるはずです。

InStrで特定の文字を含むか判別する方法

検索文字が含まれない場合は0、含まれる場合は1以上が出力されるので、条件分岐ができます。

Sub test()

Target = "hello world"
If InStr(Target, "good") > 0 Then
    MsgBox "goodが含まれます"
Else
    MsgBox "goodは含まれません"
End If

End Sub
特定の文字が含まれたら条件分岐するコード例

“good”が含まれないので条件であるInStr() > 0を満たしません。

なのでElseの方の処理が実行されます。

誤字を入れた場合

InStrは誤字を入力しても0を返します。

Sub test()

Target = "hello world"
pos = InStr(Target, "word")
MsgBox pos

End Sub

上記コードでは、”world”ではなく”word”にしました。

“wor”まで同じなので7が表示されるかと思いますが、実際は0です。

InStrRev

以下のように、”hello”が2回出てくる文字列があるとします。

Sub test()

Target = "hello world hello"
pos = InStr(Target, "hello")
MsgBox pos

End Sub

InStrは左から検索するので、実行すると1が表示されます。

InStrで検索した例

ここで左の”hello”ではなく右の”hello”の位置を知りたいとします。

そんな場合は、InStrRevを使いましょう。

Sub test()

Target = "hello world hello"
pos = InStrRev(Target, "hello")
MsgBox pos

End Sub

実行すると13が表示されるはずです。

“hello world “で12文字なので、確かに右の”hello”の位置が取得できています。

InStrRevで検索した例

ただし、出力される位置は左からの文字数なので注意してください。

InStrで特定の項目だけコピペするプログラム

以下のように、2つのファイルが存在するとします。

“Book1.xlsx”から”A”のデータのみをコピーしてみましょう。

複数の項目が含まれるデータ

①シートを指定する

Sub test()

Dim copyWs As Worksheet
Dim pasteWs As Worksheet
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)
Set pasteWs = ThisWorkbook.Worksheets(1)

End Sub

copyWsとpasteWsで、コピーするデータのあるシートと、貼り付け先のシートを定義しました。

例えば、copyWs.Cells(1, 1)と書けば”Book1.xlsx”の1枚目のA1セルを指定できます。

②”A”の行番号を取得する

今回紹介したInStrで、”A”が含まれるセルの行番号を取得しましょう!!

Sub test()

Dim copyWs As Worksheet
Dim pasteWs As Worksheet
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)
Set pasteWs = ThisWorkbook.Worksheets(1)

endRow = copyWs.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To endRow
    If InStr(copyWs.Cells(i, 1), "A") > 0 Then
        MsgBox i
    End If
Next i

End Sub

for文で2~最終行まで処理を実行し、InStrで”A”を含むか確認します。

もし含まれるなら、MsgBoxが起動して行番号が表示されるはずです。

最終行は、CountとEndを組み合わせて自動取得しました。

この手法については、以下の記事で詳しく解説しています。

実行してみて2,5,8…が表示されればOK!!あとはコピペするだけです。

③”A”のみをコピーする

先ほど取得できた行番号を使って、コピペしていきましょう。

Sub test()

Dim copyWs As Worksheet
Dim pasteWs As Worksheet
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)
Set pasteWs = ThisWorkbook.Worksheets(1)

endRow = copyWs.Cells(Rows.Count, 1).End(xlUp).Row
r = 2
For i = 2 To endRow
    If InStr(copyWs.Cells(i, 1), "A") > 0 Then
        copyWs.Cells(i, 2).copy
        pasteWs.Cells(r, 1).PasteSpecial
        Application.CutCopyMode = False
        r = r + 1
    End If
Next i

End Sub
項目”A”のデータだけコピーした図

CopyでコピーしてPasteSpecialで貼り付けます。

コピペについては以下の記事で解説しているので、ぜひご覧ください。

まとめ

今回はInStrの使い方を解説しました。

if文との相性が良いのでぜひ使ってみてください!!

VBA
スポンサーリンク
HTOMblog

コメント

タイトルとURLをコピーしました