CODEWITHSUNDEEP
vbaexcel
Kaustubh Vongole
Kaustubh Vongole

Reputation: 11

Excel manufacturing dashboard with vba

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.

Upvotes: 0

Views: 620

Answers (3)

Ronnie Smith
Ronnie Smith

Reputation: 18595

Use the filter feature / button in the Ribbon.

Upvotes: 0

Parfait
Parfait

Reputation: 107687

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

Upvotes: 1

AndrewT
AndrewT

Reputation: 498

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

Upvotes: 0

Related Questions