VBAでテーブル内の値を検索する

• 前提：ヘッダーのあるテーブル形式の範囲から、keyをもとに対象のセルを検索する。セルの検索、値の取得、値の更新をする。

Private Function searchCell(key As String, targetSheet As Worksheet, direction As String _
                          , keyPos As Long, valuePos As Long, startPos As Long, endPos As Long)

  Dim myIndex As Long, tempKey, tempValueCell As Range

  Set searchCell = Nothing
  If targetSheet Is Nothing Then Set targetSheet = ActiveSheet

  For myIndex = startPos To endPos
      If LCase(direction) = "row" Then
          tempKey = LCase(targetSheet.Cells(myIndex, keyPos).Value)
          Set tempValueCell = targetSheet.Cells(myIndex, valuePos)
      Else
          tempKey = LCase(targetSheet.Cells(keyPos, myIndex).Value)
          Set tempValueCell = targetSheet.Cells(valuePos, myIndex)
      End If

      If tempKey = "end" Then Exit For
      If tempKey = LCase(key) Then Set searchCell = tempValueCell
      If tempKey = LCase(key) Then Exit For
  Next

End Function

Private Function getValue(key As String _
                          , Optional targetSheet As Worksheet = Nothing _
                          , Optional direction As String = "row" _
                          , Optional keyPos As Long = 1 _
                          , Optional valuePos As Long = 2 _
                          , Optional startPos As Long = 2 _
                          , Optional endPos As Long = 1000)

  Dim targetCell As Range

  Set targetCell = searchCell(key, targetSheet, direction, keyPos, valuePos, startPos, endPos)
  If Not targetCell Is Nothing Then getValue = targetCell.Value

End Function

Private Function setValue(key As String _
                          , newValue _
                          , Optional targetSheet As Worksheet = Nothing _
                          , Optional direction As String = "row" _
                          , Optional keyPos As Long = 1 _
                          , Optional valuePos As Long = 2 _
                          , Optional startPos As Long = 2 _
                          , Optional endPos As Long = 1000)

  Dim targetCell As Range

  Set targetCell = searchCell(key, targetSheet, direction, keyPos, valuePos, startPos, endPos)
  If Not targetCell Is Nothing Then targetCell.Value = newValue

End Function