Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
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).
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
Check out the second item here, where is shows how to search a table for a value:
 

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Check out the second item here, where is shows how to search a table for a value:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Nov 26, 2020
Messages
98
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 
Solution

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad to hear that it all worked out!

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,645
Messages
5,654,556
Members
418,140
Latest member
ahepple86

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
Top