|
▼マナ さん:
>総務部が100件とか、沢山検索されると
>Range(256文字以上)となってエラーがでるかも。
あぁ、Range(●) の ●の文字数が多すぎるんですね。
たとえば以下とか。
Sub 検索ワードを入力して検索2()
Dim Target As String
Dim FoundCell As Range, SearchArea As Range
Dim Addr As String
Dim i As Long
Dim sAddr As Range
Target = Application.InputBox("検索ワードを入力してください", "検索", Type:=2)
If Target = "False" Then Exit Sub
Set SearchArea = ActiveSheet.UsedRange
Set FoundCell = SearchArea.Find(What:=Target, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=False, MatchByte:=False)
If FoundCell Is Nothing Then Exit Sub
Addr = FoundCell.Address
Do
If sAddr Is Nothing Then
Set sAddr = FoundCell
Else
Set sAddr = Union(sAddr, FoundCell)
End If
Set FoundCell = SearchArea.FindNext(After:=FoundCell)
i = i + 1
If FoundCell Is Nothing Then Exit Do
Loop Until FoundCell.Address = Addr
sAddr.Select
MsgBox sAddr.Count & "件見つかりました" '件数表示
End Sub
|
|