excel - How to create a unique identifier or equivalent to a dynamic table in Excel?

Question

I am assisting our artwork department to create a Graph with information in regards to time spent at each stage (Designing and approving artwork).

Originally I created a drop down filter menu and then used VLookUP to populate the relevant fields. These are all in a date format so I used the DATEDIF function to determine the days taken at each state.

I then want to populate a waterfall chart with that data.

I have recently been learning Macros and VBA and though it would be 'smarter' to create a search box to filter the data.

I was then going to duplicate that data into another table to create the source for my chart.

Unfortunately through testing I realized I cant use VlookUp or equivalent because the table collapses and the Cell identifier is no longer the same as that in the top of the list.

Is there a way I can do this? Am I going about it wrong?

The code for the search box is the following:

Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value


Dim myButton As OptionButton
Dim SearchString As String
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

'Load Sheet into A Variable
  Set sht = ActiveSheet

'Unfilter Data (if necessary)
  On Error Resume Next
    sht.ShowAllData
  On Error GoTo 0

'Filtered Data Range (include column heading cells)
  Set DataRange = sht.Range("AA21:AN28") 'Cell Range
  'Set DataRange = sht.ListObjects("Table1").Range 'Table

'Retrieve User's Search Input
  mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
  'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
  'mySearch = sht.Range("A1").Value 'Cell Input

'Determine if user is searching for number or text
  If IsNumeric(mySearch) = True Then
    SearchString = "=" & mySearch
  Else
    SearchString = "=*" & mySearch & "*"
  End If

'Loop Through Option Buttons
  For Each myButton In sht.OptionButtons
    If myButton.Value = 1 Then
      ButtonName = myButton.Text
      Exit For
    End If
  Next myButton

'Determine Filter Field
  myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)

'Filter Data
  DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:=SearchString, _
    Operator:=xlAnd

'Clear Search Field
  sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
  'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
  'sht.Range("A1").Value = "" 'Cell Input

Exit Sub

'ERROR HANDLERS
HeadingNotFound:
  MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
    vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"

End Sub

Thanks in advance

标签： excelvbams-office