都道府県コード取得マクロ ができるまで その２

はじめに

前回、
vba-belle-equipe.hatenablog.com

住所の一覧から、それぞれの都道府県名を取得できるようになりました。

今回はその都道府県名から、

このリスト（「都道府県」シート）にある都道府県コードを拾ってきましょう。

例えば「秋田県」だったとして、

B列の「秋田県」を見つけて、A列の都道府県コードをゲットするイメージです。

探す方法

一致する行を探す方法としては

1. for文で回す
2. 検索する

あたりが考えられます。

for文で回す

例によって最終行を取得して、都道府県名が一致するか一行ずつチェックします。

Sub 都道府県コード取得()
  Dim i As Long
  Dim maxRow As Long
  Dim 都道府県名 As String
  Dim 都道府県コード As String
  都道府県名 = "秋田県"
  maxRow = Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To maxRow
    If 都道府県名 = Cells(i, 2) Then
      都道府県コード = Cells(i, 1)
      Exit For
    End If
  Next
  Debug.Print 都道府県コード
End Sub

秋田県のコード「05」が出ればオッケーです。

検索する

「vba 検索」などと検索すると「Find」を使うことがわかります。

Sub 都道府県コード取得()
  Dim 都道府県名 As String
  Dim 都道府県コード As String
  都道府県名 = "秋田県"
  Dim foundCell As Variant
  Set foundCell = Cells.Find(What:=都道府県名)
  If foundCell Is Nothing Then
    Debug.Print "無いです"
  Else
    Debug.Print foundCell.Offset(0, -1)
  End If
End Sub

Findを使う時は、見つからなかった時の処理も書くのが大切なようです。

Office TANAKA - Excel VBA講座：セルの操作[セルの検索]　　参照

これくらいの件数だとfor文でもfindでもあまり差はありません。

が、大量のデータの中から探すならfindのほうが速いです。

②検索するをベースに先に進みます。

functionにする

都道府県名を伝えればコードを教えてくれる、というようにしてみましょう。

Sub test()
  Dim 都道府県名 As String
  都道府県名 = "秋田県"
  Debug.Print コード取得(都道府県名)
End Sub

Function コード取得(都道府県名 As String)
  Dim 都道府県コード As String
  Dim foundCell As Variant
  Set foundCell = Cells.Find(What:=都道府県名)
  If foundCell Is Nothing Then
    都道府県コード = ""
  Else
    都道府県コード = foundCell.Offset(0, -1)
  End If
  コード取得 = 都道府県コード
End Function

これで、メインの処理をスッキリさせることができます。

まとめてみる

前回の都道府県名取得と、今回のコード取得functionを組み合わせてみます。

Sub test()
  Dim 都道府県名 As String
  Dim i As Long
  Dim maxRow As Long
  maxRow = Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To maxRow
    都道府県名 = Left(Cells(i, 2), 4)
    Select Case 都道府県名
      Case "神奈川県", "和歌山県", "鹿児島県"
        
      Case Else
        都道府県名 = Left(都道府県名, 3)
    End Select
    Debug.Print コード取得(都道府県名)
  Next
End Sub

Function コード取得(都道府県名 As String)
  Dim 都道府県コード As String
  Dim foundCell As Variant
  Set foundCell = Sheets("都道府県").Cells.Find(What:=都道府県名)
  If foundCell Is Nothing Then
    都道府県コード = ""
  Else
    都道府県コード = foundCell.Offset(0, -1)
  End If
  コード取得 = 都道府県コード
End Function

とりあえずdebug.printで出せることを確認して

完成

もうひと息です。

Sub test()
  Dim 都道府県名 As String
  Dim i As Long
  Dim maxRow As Long
  Sheets("住所録").Activate  '(1)
  maxRow = Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To maxRow
    都道府県名 = Left(Cells(i, 2), 4)
    Select Case 都道府県名
      Case "神奈川県", "和歌山県", "鹿児島県"
        
      Case Else
        都道府県名 = Left(都道府県名, 3)
    End Select
    Cells(i, 3) = コード取得(都道府県名)  '(2)
  Next
End Sub

Function コード取得(都道府県名 As String)
  Dim 都道府県コード As String
  Dim foundCell As Variant
  Set foundCell = Sheets("都道府県").Cells.Find _
                  (What:=都道府県名)       '(3)
  If foundCell Is Nothing Then
    都道府県コード = ""
  Else
    都道府県コード = foundCell.Offset(0, -1)
  End If
  コード取得 = 都道府県コード
End Function

1. 複数のシートがからむ時は、どのシートが対象になっているか明確にしたほうがよいです
2. debug.print から　C列に書くように変更
3. ①に同じ ついでに改行

ということで、完成しました。

まとめ

世の中にはこういった類のミッションがわんさかありますが、これをベースに対応することができるのではないかと思います。

なお、ありふれた処理であるためVBAを使わなくてもできるのですが、

それはまた、別の話...