Microsoft Office Excel

Find †

[edit]

全シート検索：

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

列指定で検索

FindRowIndex("検索対象シート","A:A","検索対象文字列")

'当たりセルの行番号を返す
Function FindRowIndex(sheetName As String, cellsRange As String, columnString As String) As Integer
    Set obj = ActiveWorkbook.Sheets(sheetName).Columns(cellsRange).Find(What:=columnString, LookAt:=xlPart)

    If obj Is Nothing Then
        FindRowIndex = -1
    Else
        FindRowIndex = obj.row
    End If
End Function

FindNextで全体検索 †

[edit]

データがあるまで全部検索する

Sub 検索()
 Dim firstFindCell As Range
 Dim firstCell As Range
 Dim result As Range
 
 Set firstFindCell = Cells.Find(What:=Range("C4").Value, LookAt:=xlWhole)
 Set firstCell = firstFindCell
 Set result = firstFindCell
  
  Do
    Set firstFindCell = Range("D8:D20").FindNext(firstFindCell)
    If firstFindCell.Address = firstCell.Address Then
      Exit Do
    Else
      Set result = Union(result, firstFindCell)
    End If
  Loop
  result.Select
End Sub

コメント：

お名前:

(画像の文字列を入力して下さい)