Vbe to search for selected cell value in another table

[Xalova]

The situation is as follows:

I have a "main table" which is *full* of data. Im talkin abut 1500 entries. Then i have another table, which is for filtering. I have already figured out how to filter the table for what im looking at.

But what i now need is a way to search for a selected value from the filtered table in the main table and select it. Idk, if thats enough information, but i try to provide more:

The main table has about 10 columns. Not every column in every row is filled. Thats rarely the case. But i want to be able to enter information retropspectivly. So the best way i could think of is:
I search the table for my desired information, see that a item isnt fully descripted in the main table, select the the name of said item, click the macro button, and then VBA magic happens and it selects the Cell in the main table with the same value as the selected cell.

Im open for new ideas tho, which may be easier!

 

[Joe4]

Did it. But can i limitate the search function in such a way that it only searches a certain range (in this case a table) in another sheet for the value?

Post your code, and we can help you make that change.
Also, please tell us the sheet name you want to search, and the address of the cells you want to search (if the ending row may differ, that is fine, just let us know which row the data starts on, and what columns are to be included).

 

[Xalova]

Post your code, and we can help you make that change.
Also, please tell us the sheet name you want to search, and the address of the cells you want to search (if the ending row may differ, that is fine, just let us know which row the data starts on, and what columns are to be included).

i do not exactly know what you mean, but here is my slightly altered vba code. What i changed is that the value it searches for is the ActiveCell value

Sub Search()
    Cells.Find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate
End Sub

the sheet name i want to search in is "G-Nummern".

i dont get what you meant with the "adress of the cells", but i want to search in a table named "Verbrauchsmaterial"

 

[Joe4]

Check out the second item here, where is shows how to search a table for a value:

Excel VBA Find: 20 Examples to Search and Find with Macros

Quickly learn to search and find different items/information using macros. 20 easy-to-adjust step by step VBA code examples you can use right now.

powerspreadsheets.com

 

[Xalova]

Check out the second item here, where is shows how to search a table for a value:

Excel VBA Find: 20 Examples to Search and Find with Macros

Quickly learn to search and find different items/information using macros. 20 easy-to-adjust step by step VBA code examples you can use right now.

powerspreadsheets.com

sadly thats not what i looked for. What i need is, since the table is so huge, to select the cell with the desired cell value, so i dont have to search it manually. As it does with the normal "find" function (by pressing ctrl+f). Though i just need it to search in another sheet, instead of the active sheet.

 

[Joe4]

Did you take a look at the VBA code, the function, in that section?
It will return the address of the cell that the value is found in.
The functions takes three arguments - the sheet name, the value you are looking for, and the table name.
So it should have everything that you are looking for.

It will return the cell address. Once you have that, you can select that cell.

 

[Xalova]

Did you take a look at the VBA code, the function, in that section?
It will return the address of the cell that the value is found in.
The functions takes three arguments - the sheet name, the value you are looking for, and the table name.
So it should have everything that you are looking for.

It will return the cell address. Once you have that, you can select that cell.

I did take a look at that. And, uh, i am confused. I am confused as to where i put my table name. Also this whole thing is a function, but i need a macro.

 

[Tupe77]

Do you have any idea how you wanted to do that? There are at least three ways to accomplish that. 1. Jump to the main table and edit. 2. User form to fill in the information. 3. Rows pinned above the auxiliary table where the data is filtered and then returned back to the main table. I wasn’t familiar with your formulas, so I ask: A row in the filter table always matches one whole row in the main table?

What would act as a macro trigger?

I may not be the right person to help with that, but probably defining a goal here will at least make it easier to find the right person.

 

[Joe4]

You create Macros to simplify and automate things. Your can create your own functions for the same reason.
A User Defined Function (UDF) is a type of VBA code that returns a value, instead of just performing actions like a Sub Procedure (aka "Macro") does.
It works like any other Excel function, and can be used either right on the worksheet, or in VBA.

You could actually imbed the code for the function right into your Sub Procedure, but splitting it out into its own function has some advantages, such as:
1. It does not "clog up" the code in your main Sub Procedure
2. It can easily be called and re-used multiple times in your code with a simple function call (instead of having to rewrite the code each time)
3. As mentioned previously, it can be used both in VBA code and directly on the worksheet

And the good thing is, you do NOT need to modify that function at all! They already wrote it for you. All you have to do is to copy and paste it, as-is, to your VBA module (under your current Sub procedure).

I am sure that you are used to using other native Excel functions that have multiple arguments (like IF, VLOOKUP, etc). This works exactly the same way. Just populate the arguments. Note that the third argument is Table Index, not Table Nable Name. If it is the only table on your "G-Nummern" sheet, is is probably 1.

So see the code below, where I did an example based on your specifications. If you select the cell that has the value you want to look up and then run the "MyFind" Sub Procedure, you will see it does what you want:

Sub MyFind()

    Dim rng As String
    
'   Look up value from active cell and get cell address it is located in
    rng = FindValueInTable("G-Nummern", ActiveCell.Value, 1)
    
'   Select cell on "G-Nummern" sheet
    Sheets("G-Nummern").Activate
    Range(rng).Select
    
End Sub


Function FindValueInTable(MyWorksheetName As String, MyValue As Variant, Optional MyTableIndex As Long = 1) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
     
    'This UDF:
        '(1) Accepts 3 arguments: MyWorksheetName, MyValue and MyTableIndex
        '(2) Finds a value passed as argument (MyValue) in an Excel Table stored in a worksheet whose name is passed as argument (MyWorksheetName). The index number of the Excel Table is either:
            '(1) Passed as an argument (MyTableIndex); or
            '(2) Assumed to be 1 (if MyTableIndex is omitted)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the Excel Table (stored in the MyWorksheetName worksheet and whose index is MyTableIndex) where the value (MyValue) is found
     
    With ThisWorkbook.Worksheets(MyWorksheetName).ListObjects(MyTableIndex).DataBodyRange
        FindValueInTable = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
     
End Function

 

[Xalova]

You create Macros to simplify and automate things. Your can create your own functions for the same reason.
A User Defined Function (UDF) is a type of VBA code that returns a value, instead of just performing actions like a Sub Procedure (aka "Macro") does.
It works like any other Excel function, and can be used either right on the worksheet, or in VBA.

You could actually imbed the code for the function right into your Sub Procedure, but splitting it out into its own function has some advantages, such as:
1. It does not "clog up" the code in your main Sub Procedure
2. It can easily be called and re-used multiple times in your code with a simple function call (instead of having to rewrite the code each time)
3. As mentioned previously, it can be used both in VBA code and directly on the worksheet

And the good thing is, you do NOT need to modify that function at all! They already wrote it for you. All you have to do is to copy and paste it, as-is, to your VBA module (under your current Sub procedure).

I am sure that you are used to using other native Excel functions that have multiple arguments (like IF, VLOOKUP, etc). This works exactly the same way. Just populate the arguments. Note that the third argument is Table Index, not Table Nable Name. If it is the only table on your "G-Nummern" sheet, is is probably 1.

So see the code below, where I did an example based on your specifications. If you select the cell that has the value you want to look up and then run the "MyFind" Sub Procedure, you will see it does what you want:

Sub MyFind()

    Dim rng As String
   
'   Look up value from active cell and get cell address it is located in
    rng = FindValueInTable("G-Nummern", ActiveCell.Value, 1)
   
'   Select cell on "G-Nummern" sheet
    Sheets("G-Nummern").Activate
    Range(rng).Select
   
End Sub


Function FindValueInTable(MyWorksheetName As String, MyValue As Variant, Optional MyTableIndex As Long = 1) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 3 arguments: MyWorksheetName, MyValue and MyTableIndex
        '(2) Finds a value passed as argument (MyValue) in an Excel Table stored in a worksheet whose name is passed as argument (MyWorksheetName). The index number of the Excel Table is either:
            '(1) Passed as an argument (MyTableIndex); or
            '(2) Assumed to be 1 (if MyTableIndex is omitted)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the Excel Table (stored in the MyWorksheetName worksheet and whose index is MyTableIndex) where the value (MyValue) is found
    
    With ThisWorkbook.Worksheets(MyWorksheetName).ListObjects(MyTableIndex).DataBodyRange
        FindValueInTable = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

thank you for bearing with me and helping me to finish this project. Even though i was hard to understand This works like a charm.

 

[Joe4]

You are welcome.
Glad to hear that it all worked out!