Table: Find count of search value match and return all Table rows

[inddon]

Hello There,

I have a sub which search a given value in a Table and returns a single row to the worksheet.

There might be cases that the given search value in a Table can have multiple rows. I would like a construction that will get the total number of rows found for the given search value in the Table. If returned search is greater than 1 then it should display the rows accordingly.

Please find attach a sample file for your reference.

Look forward to hearing from you.

Below are the sub construction which I am using. I would like to keep the same concept of first checking via the function if search value exists as it used across the entire workbook.

Regards,
Don



Sub to get Table values and display in worksheet, if search found

Sub DisplayTableValues()
   Dim EntWB As Workbook, DispWS As Worksheet
   Dim MstWSName_Transaction As Worksheet, MstTblName_Transaction As String, O_MstTbl_Transaction As Variant 'ListObjects
   Dim TblSearchColumn As String, TblSearchColumnValue As String
   Dim fnd As Range
   
   Set EntWB = ThisWorkbook
   Set DispWS = EntWB.Worksheets("Display")
   Set MstWSName_Transaction = EntWB.Worksheets("Transaction")
   MstTblName_Transaction = "Tbl_Transaction"
   Set O_MstTbl_Transaction = MstWSName_Transaction.ListObjects("Tbl_Transaction") '(MstTblName_Transaction)

   TblSearchColumn = "Customer Number"
   TblSearchColumnValue = "1001"
   
   If CheckValueExistsInTable(O_MstTbl_Transaction, TblSearchColumn, TblSearchColumnValue) Then
      
      DispWS.[FSr] = vbNullString
      DispWS.[FInvoiceNumber] = vbNullString
      DispWS.[FCustomerNumber] = vbNullString
      
      'Display totla rows found
      DispWS.FTotalRows = 0
      
      If DispWS.FTotalRows > 1 Then
        'Enable All Buttons
      End If
      
      'Check Value found in Master Table
      Set fnd = O_MstTbl_Transaction.ListColumns(TblSearchColumn).DataBodyRange.Find(What:=TblSearchColumnValue, LookIn:=xlValues, LookAt:=xlPart) 'xlWhole)
       
      DispWS.[FSr] = Intersect(O_MstTbl_Transaction.ListColumns("Sr.").DataBodyRange, fnd.EntireRow).Value
      DispWS.[FInvoiceNumber] = Intersect(O_MstTbl_Transaction.ListColumns("Invoice Number").DataBodyRange, fnd.EntireRow).Value
      DispWS.[FCustomerNumber] = Intersect(O_MstTbl_Transaction.ListColumns("Customer Number").DataBodyRange, fnd.EntireRow).Value
   Else
     MsgBox TblSearchColumnValue & " not found."
     
     'Disable All Buttons
   End If
End Sub

Function to Check if the Given Search value exists in Table

Function CheckValueExistsInTable(ByVal pMasterTableName As ListObject, ByVal pMasterTableColumnName As String, ByRef pFieldRangeNameValue As String) As Boolean
  Dim fnd1 As Range

  'Check Value found in Master Table
  Set fnd1 = pMasterTableName.ListColumns(pMasterTableColumnName).DataBodyRange.Find(What:=pFieldRangeNameValue, LookIn:=xlValues, LookAt:=xlPart) 'xlWhole)

  If Not fnd1 Is Nothing Then
    CheckValueExistsInTable = True
  Else
    CheckValueExistsInTable = False
  End If
End Function

 

[Marc L]

Hi !​

​

According to the attachment, paste this code to the Display worksheet module :​

  Const T = "Tbl_Transaction[Customer Number]"
    Dim R&, V

Sub Update(L&, Optional TOTAL As Boolean)
        R = L
        Application.EnableEvents = False
    If UBound(V) < 0 Then
        Union([FTotalRows], [FSr], [FInvoiceNumber]).Value2 = ""
        Me.Shapes.Range([{"CmdEnd","CmdNext","CmdPrevious","CmdStart"}]).Visible = False
    Else
        If TOTAL Then [FTotalRows].Value2 = UBound(V) + 1
        [FSr].Value2 = Sheet1.Cells(V(R), Sheet1.Range(T).Column - 2).Value2
        [FInvoiceNumber].Value2 = Sheet1.Cells(V(R), Sheet1.Range(T).Column - 1).Value2
        Me.Shapes.Range([{"CmdEnd","CmdNext"}]).Visible = R < UBound(V)
        Me.Shapes.Range([{"CmdPrevious","CmdStart"}]).Visible = R > 0
    End If
        Application.EnableEvents = True
End Sub

Private Sub CmdEnd_Click()
    Update UBound(V)
End Sub

Private Sub CmdNext_Click()
    Update R + 1
End Sub

Private Sub CmdPrevious_Click()
    Update R - 1
End Sub

Private Sub CmdStart_Click()
    Update 0
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target(1).Address = [FCustomerNumber].Address Then
        V = Target(1).Value2
        If V > "" Then V = Filter(Sheet1.Evaluate("TRANSPOSE(IF(" & T & "=" & V & ",ROW(" & T & ")))"), False, False) Else V = Split(V)
        Update 0, True
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[inddon]

Beautiful code Marc. No words but just thanks a lot. This is something very useful.

Just another thought. When a search is found in function CheckValueExistsInTable, then is it possible to store only the [Sr.] numbers in an array?

Regards,
Don

 

[Marc L]

​

You could loop using Find method like in its sample in VBA help​

or just using a formula like in my code within the Change event …​

​

 

[inddon]

Thank you Marc

I added the following in the change event and it works

Const G = "Tbl_Transaction[Sr.]"

and G to include in the formula:

V = Filter(Sheet1.Evaluate("TRANSPOSE(IF(" & T & "=" & V & "," & G & "))"), False, False)

However, I was not able to get the loop using Find Method. Just curious. Could you please show it in a code?

Regards,
Don

 

[Marc L]

Use for example COUNTIF worksheet function to obtain the number of corresponding elements​

then create an array accordingly (via Redim) then use the Range.Find method like in the VBA help …​

​

That's the reason why I prefer to respect the main rule via a direct formula which returns an array !​

​

 

[inddon]

Thank you Marc.

I guess the method you showed in the change event is easir to follow. I will stick with that.

Regards,
Don

 

[Marc L]

Another way with the Range.Find method is to store data in a delimited string and use at the end the Split function …​

​

In real life people prefer to see all rows corresponding to the search,​

so there is nothing to code with a table with its yet existing filters !​

​

And when the data must be extracted to another place, just with worksheet(s) respecting Excel spirit,​

the code often needs only a single codeline via an advanced filter ‼​

​

Sometimes that does not ever need any code just with a pivot for example …​

​

 

[inddon]

Thank you Marc

 

[inddon]

Hi Marc,

I am adding some calculations based on the V variable. When I do a loop of the V, for i = 1 to UBound(V), I see the first value is not shown.
V = Filter(Sheet1.Evaluate("TRANSPOSE(IF(" & T & "=" & V & "," & G & "))"), False, False)

I used the redim and also included option base 1, in vain

How can th above code be formatted so the value are strting from 1 instead of 0 (zero) during loop?

Regards,
Don

 

[Marc L]

As written in VBA help of Filter function, the array is zero base so the first element is V(0), the 2nd V(1), …​

Like you can see twice within my code as the Update starts from zero.​

See also LBound & UBound VBA functions.​

​