모두 검색 (Microsoft Excel)

Sub selCellbasedonValue()

Dim c As Object     Dim u As Range     Dim v As Range     Dim sInpt As String

Set u = ActiveSheet.UsedRange

sInpt = InputBox("Enter the search text")



If sInpt > "" Then         For Each c In u             If Instr(LCase(sInpt),LCase(c.Value)) > 0 Then                 If v Is Nothing Then                     Set v = Range(c.Address)

Else                     Set v = Union(v, Range(c.Address))

End If             End If         Next         v.Select         Set v = Nothing     End If

Set u = Nothing End Sub

Sub CopyFinds()

Dim sSrch As String     Dim sFirst As String     Dim rPaste As Range     Dim i As Integer     Dim iLeftC As Integer     Dim lTopR As Long     Dim c As Object

If Selection.Cells.Count = 1 Then         MsgBox "Select the range to be searched."

Exit Sub     End If

'Specify search string     sSrch = InputBox(Prompt:="Enter the search text")



'   Set the paste address     On Error Resume Next     Set rPaste = Application.InputBox(Prompt:="Enter the upper-left " & _       "cell address for the paste range", Type:=8)

On Error GoTo 0

'   Exit if canceled     If TypeName(rPaste) <> "Range" Then Exit Sub

'   Upper left cell to be used     Set rPaste = rPaste.Range("A1")



'Set where paste will start and headings     Application.ScreenUpdating = False     lTopR = rPaste.Row     iLeftC = rPaste.Column     Cells(lTopR, iLeftC) = "Address"

Cells(lTopR, iLeftC + 1) = "Cell Value"

lTopR = lTopR + 1          'Start copying cell values     With Selection         Set c = .Find(What:=sSrch, LookAt:=xlPart, MatchCase:=True)

If Not c Is Nothing Then             sFirst = c.Address             Do                 Cells(lTopR, iLeftC) = c.Address                 Cells(lTopR, iLeftC + 1) = c.Value                 Set c = .FindNext(c)

lTopR = lTopR + 1             Loop While Not c Is Nothing And c.Address <> sFirst         End If     End With          Application.ScreenUpdating = True     Cells(rPaste.Row, rPaste.Column).Select End Sub