【セル検索（存在しない場合を考慮）】

ここを編集

セル検索

Dim rFindResult As Range
Dim sSrchKeyword As String
Dim lSrchCellRow As Long
Dim lSrchCellClm As Long
 
With ThisWorkbook.Sheets(INPUT_SHEET_NAME)
    sSrchKeyword = KYWD_INPUT_DIR_PATH
    Set rFindResult = .Cells.Find(sSrchKeyword, LookAt:=xlWhole)
    If rFindResult Is Nothing Then
        MsgBox sSrchKeyword & "が見つかりませんでした"
    Else
        lSrchCellRow = rFindResult.Row
        lSrchCellClm = rFindResult.Column
    End If
End With
 

直近セル情報取得

Public Type T_EXCEL_NEAR_CELL_DATA
    bIsCellDataExist As Boolean
    lRow As Long
    lClm As Long
    sCellValue As String
End Type
 
Public Function GetNearCellData( _
    ByVal shTrgtSht As Worksheet, _
    ByVal sSrchKey As String, _
    ByVal lRowOffset As Long, _
    ByVal lClmOffset As Long _
) As T_EXCEL_NEAR_CELL_DATA
    Dim rFindResult As Range
 
    Set rFindResult = shTrgtSht.Cells.Find(sSrchKey, LookAt:=xlWhole)
    If rFindResult Is Nothing Then
        GetNearCellData.bIsCellDataExist = False
    Else
        GetNearCellData.bIsCellDataExist = True
        GetNearCellData.lRow = rFindResult.Row + lRowOffset
        GetNearCellData.lClm = rFindResult.Column + lClmOffset
        GetNearCellData.sCellValue = shTrgtSht.Cells( _
                                            GetNearCellData.lRow, _
                                            GetNearCellData.lClm _
                                         ).Value
    End If
End Function
 

ここを編集