I have an excel sheet detailing a list of equipment (about 12000 rows in the sheet). I want to make a dashboard whereby I enter the name of the equipment and it returns the Manufacturer, Date of manufacture and description.
What would be the best way to go about this? I was thinking of writing VBA code in order to match an input to an object type and return the required value however I have not coded in VBA and I'm unsure how to type it out.
Forgive me in suggesting a different approach but consider the scalable, relational advantage of SQL (Structured Query Language) that can take the Equipment Name as parameter and query your data into a filtered table resultset. If using Excel for PC, Excel can run SQL using the Jet/ACE SQL Engine (Windows .dll files), the very engine that powers its sibling, MS Access. This approach avoids array formulas, loops, if/then logic, complex multiple index/matching, and vlookups.
Below example prompts user for the Equipment Name using an InputBox which is then passed as a parameter to the WHERE clause of SQL query. We hate for a malicious user to run SQL injection in input box, something like: 1; DELETE FROM [DATA];. Example assumes data exists in a tab called DATA with column headers in first row and an empty tab called RESULTS. Adjustments can be made.
Sub RunSQL()
Dim conn As Object, rst As Object, cmd As Object
Dim equipmentVar As String, strConnection As String, strSQL As String
Dim i As Integer
Const adcmdText = 1, adVarChar = 200, adParamInput = 1
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
' RECEIVE USER INPUT
equipmentVar = InputBox("Enter name of equipment.", "EQUIPMENT SEARCH")
If equipmentVar = "" Then Exit Sub
' CONNECTION STRINGS (TWO VERSIONS)
' strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
' & "DBQ=C:\Path\To\Workbook.xlsm;"
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source='C:\Path\To\Workbook.xlsm';" _
& "Extended Properties=""Excel 8.0;HDR=YES;"";"
strSQL = " SELECT [DATA$].Manufacturer, [DATA$].Equipment, " _
& " [DATA$].[Date of Manufacturer], [DATA$].[Description] " _
& " FROM [DATA$]" _
& " WHERE [DATA$].[Equipment] = ?;"
' OPEN DB CONNECTION
conn.Open strConnection
' SET CMD COMMAND
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = conn
.CommandText = strSQL
.CommandType = adcmdText
.CommandTimeout = 15
End With
' BINDING PARAMETER
cmd.Parameters.Append cmd.CreateParameter("equipParam", adVarChar, adParamInput, 255)
cmd.Parameters(0).Value = equipmentVar
' EXECUTING TO RECORDSET
Set rst = cmd.Execute
' COLUMN HEADERS
For i = 1 To rst.Fields.Count
Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
Next i
' DATA ROWS
Worksheets("RESULTS").Range("A2").CopyFromRecordset rst
rst.Close: conn.Close
Set rst = Nothing: Set cmd = Nothing: Set conn = Nothing
End Sub
Sounds like a simple loop through the cells looking for a match, because you have not given the layout I can't tell you the exact code but you are asking for an approach so here it goes.
1) If you want it to work when you enter a value in the cell then you use worksheet_Change i.e. Options Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
' Your code here
End If
End Sub
The quickest way to code a loop through your 12000 rows but could be any length in future (assuming they start at row 5)
Dim cell As Range
For Each cell In Range("a5", Range("a" & Cells.Rows.Count).End(xlUp))
'Your if statement to determin if it is a match goes here
'Your copy code goes here
Next cell
2)You then put in the If statement to check for a match and do any copy display you want when you find one.
Good luck
