Search Multi tables and to tables

donkey131

New Member
Joined
Apr 12, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I have managed to learn much from this great Excel forum BUT im stuck a little and in need of a little help
i have added the file for people to look at
need help no the search buttom from the index and also the add buttom
i would like to search on the selected category all All the tables if blank
for the add button like to add the data to the bottom of the selected table once sorted A-Z and to make sure there isnt a same data in part number.
i know its asking much but some help would be great.
im a novice think i done well so far just from your site

many thanks
I know you need to be member of this forum to get the file but no way i see to add file on this topic page
Excel Help Forum
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Assuming for the moment that you wanted to search any Sheet/Table if no category was selected, and only that Sheet/Table if one was selected from the combobox, you could use something like this for the search button click event:

VBA Code:
Private Sub cmdSearch_Click()

    Dim Sheet As Worksheet
    Dim Table As ListObject
    Dim FoundRow As Long
    
    On Error Resume Next
    Set Table = ThisWorkbook.Worksheets(Me.ListBox1.Value).ListObjects(1)
    On Error GoTo 0
    
    If Not Table Is Nothing Then
        FoundRow = GetTableRow(Table, Me.SearchBox1.Value, Table.ListColumns("Part #").Index)
        If FoundRow > 0 Then
            MsgBox "Value of '" & Me.SearchBox1.Value & "' was found on sheet '" & Table.Parent.Name & "' (Table name of '" & Table.DisplayName & "') in row " & FoundRow & "."
        Else
            MsgBox "Value of '" & Me.SearchBox1.Value & "' was not found."
        End If
    Else
        
        For Each Sheet In ThisWorkbook.Worksheets
            For Each Table In Sheet.ListObjects
                FoundRow = GetTableRow(Table, Me.SearchBox1.Value, Table.ListColumns("Part #").Index)
                    If FoundRow > 0 Then
                        MsgBox "Value of '" & Me.SearchBox1.Value & "' was found on sheet '" & Table.Parent.Name & "' (Table name of '" & Table.DisplayName & "') in row " & FoundRow & "."
                        GoTo ExitLoop
                    End If
            Next Table
        Next Sheet
        
        MsgBox "Value of '" & Me.SearchBox1.Value & "' was not found."
ExitLoop:
        
    End If
    
End Sub


Private Function GetTableRow( _
    ByVal Table As ListObject, _
    ByVal ValueToFind As Variant, _
    ByVal ColumnIndex As Long _
    ) As Long

    On Error Resume Next
    GetTableRow = Application.Match(ValueToFind, Table.ListColumns(ColumnIndex).DataBodyRange, 0)
    On Error GoTo 0
    
End Function

For the add portion, assuming you had a command button named 'cmbAdd', you could use something like this:

VBA Code:
Private Sub cmbAdd_Click()

    Dim Table As ListObject
    Dim FoundRow As Long
    
    On Error Resume Next
    Set Table = ThisWorkbook.Worksheets(Me.ListBox1.Value).ListObjects(1)
    On Error GoTo 0
    
    If Not Table Is Nothing Then
        
        FoundRow = GetTableRow(Table, Me.SearchBox1.Value, Table.ListColumns("Part #").Index)
        If FoundRow = 0 Then
            Table.ListRows.Add
            Table.ListRows(Table.ListRows.Count).Range(1, 2).Value = Me.SearchBox1.Value
            MsgBox "Value of '" & Me.SearchBox1.Value & "' added to '" & Table.Parent.Name & "' (Table name of '" & Table.DisplayName & "') at row " & Table.ListRows.Count & "."
        Else
            MsgBox "That value is already in the table."
        End If
        
    Else
        
        MsgBox "The table could not be found."
        
    End If
    
End Sub
 
Upvote 0
thanks for trying to help, but having issuse as not able to get it working and i dont fully understand searching multi table on different sheet in excel
VB to print the information in the result box.
 
Upvote 0
Works fine for me. If it doesn't work as expected, please be more specific about your expectations. Please be very, very specific. The more information you give, the more we can help.
 
Upvote 0
ok I did try to put the file link but so you people could see on what i had already done.
just the two button in the index of search and add. the search was to put a wild card name into the seach box and if category is blank then search all tables on each sheet or if category is selected then just search that table.
for the add form
is too check is field is blank and say to enter in data then to add the data to the correct selected category table.
 
Upvote 0
You're still not giving many details. You have two userforms. The code I posted above were for usage in 'UserFormSearch'. Originally I thought you were going to add at the same time you searched, while using 'UserFormSearch', but now I'm thinking you're wanting to use them both individually for their respective functionalities. If that is the case, move the function to a standard module and declare it public instead of private. I would make some slight changes in case you have numbers instead of text and where you're only passing the column header text of the Table, since you're already passing the Table object. It looks like this:

VBA Code:
Public Function GetTableRow( _
    ByVal Table As ListObject, _
    ByVal ValueToFind As Variant, _
    ByVal ColumnName As String _
    ) As Long

    On Error Resume Next
    If IsNumeric(ValueToFind) Then
        GetTableRow = Application.Match((ValueToFind * 1), Table.ListColumns(ColumnName).DataBodyRange, 0)
    Else
        GetTableRow = Application.Match(ValueToFind, Table.ListColumns(ColumnName).DataBodyRange, 0)
    End If
    On Error GoTo 0
    
End Function

In your 'UserFormAdd', change the CMD_ADD_Click method to this:

VBA Code:
Private Sub CMD_ADD_Click()

    Dim Table As ListObject
    Dim FoundRow As Long
    
    On Error Resume Next
    Set Table = ThisWorkbook.Worksheets(Me.ComboBox1.Value).ListObjects(1)
    On Error GoTo 0
    
    If Not Table Is Nothing Then
        
        FoundRow = GetTableRow(Table, Me.TextBox2.Value, "Part #")
        If FoundRow = 0 Then
            Table.ListRows.Add
            Table.ListRows(Table.ListRows.count).Range(1, 1).Resize(1, 5).Value = Array(Me.TextBox3.Value, Me.TextBox2.Value, Me.TextBox4.Value, Me.ComboBox2.Value, Me.TextBox5.Value)
            MsgBox "Value of '" & Me.TextBox2.Value & "' added to '" & Table.Parent.Name & "' (Table name of '" & Table.DisplayName & "') at row " & Table.ListRows.count & "."
        Else
            MsgBox "That value is already in the table."
        End If
        
    Else
        
        MsgBox "The table could not be found."
        
    End If
    
End Sub

In your userform 'UserFormSearch', there are slight changes to cmdSearch_Click method:

VBA Code:
Private Sub cmdSearch_Click()

    Dim Sheet As Worksheet
    Dim Table As ListObject
    Dim FoundRow As Long
    
    On Error Resume Next
    Set Table = ThisWorkbook.Worksheets(Me.ListBox1.Value).ListObjects(1)
    On Error GoTo 0
    
    If Not Table Is Nothing Then
        FoundRow = GetTableRow(Table, Me.SearchBox1.Value, Table.ListColumns("Part #").Index)
        If FoundRow > 0 Then
            MsgBox "Value of '" & Me.SearchBox1.Value & "' was found on sheet '" & Table.Parent.Name & "' (Table name of '" & Table.DisplayName & "') in row " & FoundRow & "."
        Else
            MsgBox "Value of '" & Me.SearchBox1.Value & "' was not found."
        End If
    Else
        
        For Each Sheet In ThisWorkbook.Worksheets
            For Each Table In Sheet.ListObjects
                FoundRow = GetTableRow(Table, Me.SearchBox1.Value, "Part #")
                    If FoundRow > 0 Then
                        MsgBox "Value of '" & Me.SearchBox1.Value & "' was found on sheet '" & Table.Parent.Name & "' (Table name of '" & Table.DisplayName & "') in row " & FoundRow & "."
                        GoTo ExitLoop
                    End If
            Next Table
        Next Sheet
        
        MsgBox "Value of '" & Me.SearchBox1.Value & "' was not found."
ExitLoop:
        
    End If
    
End Sub

Please take note of the below line of code from the Add userform:

VBA Code:
            Table.ListRows(Table.ListRows.count).Range(1, 1).Resize(1, 5).Value = Array(Me.TextBox3.Value, Me.TextBox2.Value, Me.TextBox4.Value, Me.ComboBox2.Value, Me.TextBox5.Value)

This is very confusing to read/consume and serves as an excellent example as to why its a good idea to name your controls with meaningful names. Looking at this code you would have absolutely no idea what this is writing unless you were extremely intimate with your userform and knew all of the control names inside and out. "TextBox3" doesn't tell me what kind of data it should house. Looking at this nobody would know that it's the Quantity field, or that "TextBox5" is the Location field.

I also recommend setting tab orders, cancel and default settings. Tab order is important to user expectation and flow (UI/UX). Setting a button to Cancel being True can be helpful if you want the user to be able to use the ESC key while in the userform. Conversely the Default can be set to True for the button you want actuated when the user presses ENTER.
 
Upvote 0
very sorry for the poor coding of my work but i have only just start last month to teach myself.
i shall try your code and do my best to get what i expecting
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top