Submitted By: Halim

VBA macro that searches a spreadsheet by rows and by values.

Visual Basic
Edit|Remove
Private Sub CommandButton2_Click()
    Dim FIN As Object, Cariin As String, teks As Long
    ListBox1.ColumnCount = 2
    ListBox1.Clear
    Cariin = TextBox1.Value
    If Cariin = "" Then Exit Sub
    teks = ActiveSheet.UsedRange.Address
    
    For t = Len(teks) To 1 Step -1
        cari$ = Mid(teks, t, 1)
        If cari$ = "$" Then
            txt = Mid(teks, t, Len(teks) - 1)
            Exit For
        End If
    Next t
    
    For I = ActiveSheet.UsedRange.Row To Mid(txt, 2, Len(txt))
        Set FIN = ActiveSheet.Rows(I).Find(Cariin, LookIn:=xlValues)
        If Not FIN Is Nothing Then
            ListBox1.AddItem FIN.Address
            ListBox1.List(R, 1) = FIN.Value
            R = R + 1
        End If
    Next I
    
End Sub

Private Sub ListBox1_Click()
    On Error GoTo g
    Range(ListBox1.List(ListBox1.ListIndex, 0)).Select
g:
End Sub