Searching for multiple values using VBA in Excel

Question

I do not know if it is possible to implement this problem in VBA or it must be done with VB. Net using Visual Studio.

Problem: Excel has its search function and it is a pain if there many value available or you must find a value that far away from column A.

I would like to have something like this

In which I can specify what columns I want to display by their header name. Rearrange the column in the way I would like to, and ability to copy and paste. Like datagrid in Visual basic? Is it possible?

Solution

I have given below both the methods - VBA and VB.net (take your pick) :)

USING VB.NET

Place a DataGridView on your VB.net Form and also place a Button. Place this code in the Button

Public Class Form1
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim masterTable As New DataTable

        Dim cnnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0 Xml;HDR=NO"";Data Source=""{0}"";"

        Using da As New OleDb.OleDbDataAdapter("select * from [Sheet1$] Where F1 = 'Test1'", String.Format(cnnStr, "C:\Book1.xlsx"))
            da.Fill (masterTable)
        End Using
        DataGridView1.DataSource = masterTable
    End Sub
End Class

And you are done :)

SNAPSHOT

I am using limited Data.

You can also change your string "select * from [Sheet1$] Where F1 = 'Test1'" to "select F1 as Name,F2 as PN, F3 as [Inventory Loc] from [Sheet1$] Where F1 = 'Test1'" to display the headers as below

EDIT

In case you are wondering how to do it in VBA

USING VBA

Place a Listbox and a Command Button on a Form and then use this code.

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim Ar As Variant

    Set ws = Sheets("Sheet1")

    lastRow = ws.Cells.Find(What:="*", After:=ws.Range("A1"), _
              Lookat:=xlPart, LookIn:=xlFormulas, _
              SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
              MatchCase:=False).Row

    Set rng = ws.Range("A1:C" & lastRow)

    Set ws1 = Sheets.Add

    With rng
        ws.AutoFilterMode = False
        .AutoFilter Field:=1, Criteria1:="Test1"
        .SpecialCells(xlCellTypeVisible).Copy ws1.Range("A1")
        ws.AutoFilterMode = False

        lastRow = ws1.Cells.Find(What:="*", After:=ws1.Range("A1"), _
                  Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, MatchCase:=False).Row

        Ar = ws1.Range("A1:C" & lastRow)

        Application.DisplayAlerts = False
        ws1.Delete
        Application.DisplayAlerts = True
    End With

    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = 3
        .List = Ar
        .ColumnWidths = "50;50;50"
        .TopIndex = 0
    End With
End Sub

SNAPSHOT

MORE FOLLOWUP

Hi Siddharth, thank you very much for both code. For VB. Net it is wonderful. For the VBA in Exel, is there any way that I can copy (using Ctrl + C) to copy the data - single row will be fine, although copy multiple rows is more desirable. I am able to replace "Test1" with textbox i49.tinypic.com/2ceq3yf.jpg – user1370854 5 hours ago

Yes it is possible to copy the single selected or multiple selected items from the listbox to cliboard. To make the listobx multiselect, in the design mode, set the property of the listbox to fmMultiSelectMulti1. Next Add a command button and paste this code.

This code is again based on the data I use above so amend it as applicable. When you press the Copy button, the data is copied to the clipboard and then you can simply use CTL V to paste the data where ever you want; for example in Notepad.

Private Sub CommandButton2_Click()
    Dim MyData As DataObject
    Dim i As Long
    Dim strCopiedText As String

    Set MyData = New DataObject

    With Me.ListBox1
        For i = 1 To .ListCount
            If .Selected(i - 1) Then
                strCopiedText = strCopiedText & _
                                .List(i - 1, 0) & vbTab & _
                                .List(i - 1, 1) & vbTab & _
                                .List(i - 1, 2) & vbCrLf
            End If
        Next i

        If Len(strCopiedText) > 0 Then
            MyData.Clear
            MyData.SetText strCopiedText
            MyData.PutInClipboard
            MsgBox "Data copied to clipboard"
        End If
    End With
End Sub