| |
▼UO3 さん：
Findメソッドでも列ごとに検索したものを
UnionでRow順にしてCollectionにでも入れてあげれば出来そうですよ^^
Sub test()
Dim tbl As Variant
Dim FindStr As Variant
Dim myColumn As Range
Dim FoundRng As Range
Dim UnionRng As Range
Dim myC As New Collection
With ActiveSheet
tbl = Split(.Range("A1").Value, " ")
For Each myColumn In .UsedRange.Columns
For Each FindStr In tbl
Set FoundRng = myColumn.Find(FindStr, myColumn.Cells(1), _
xlValues, xlPart, xlByRows, xlNext)
If Not FoundRng Is Nothing Then
FirstAddress = FoundRng.Address
Do
If Application.Intersect(FoundRng, .Range("A1")) Is Nothing Then
If UnionRng Is Nothing Then
Set UnionRng = FoundRng
Else
Set UnionRng = Application.Union(UnionRng, FoundRng)
End If
End If
Set FoundRng = myColumn.FindNext(FoundRng)
Loop Until FirstAddress = FoundRng.Address
End If
Next FindStr
If Not UnionRng Is Nothing Then
For Each myRng In UnionRng
myC.Add myRng
Next myRng
Set UnionRng = Nothing
End If
Next myColumn
End With
If myC.Count > 0 Then
For Each FoundRng In myC
MsgBox "次へ"
FoundRng.Select
Next FoundRng
Else
MsgBox "見つかりません"
End If
End Sub
|
|
ｍｏｍｏ
- 11/10/7(金) 15:22 -
