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!
 

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
You are welcome.
Glad to hear that it all worked out!

:)
it worked perfectly. up until now. though i must say that i copied and pasted the functions to a different workbook, but it should do the same. Now what i want to search for is the current date and let the macro select the cell with the current date in the table for me. But... it doesnt work. it simply just doesnt like any kind of date. i tried it in many different ways. but all are unseccessfull. Do you have any idea?

the perfect solution would be

VBA Code:
Sub MyFind()

    Dim rng As String
 '   Look up value from active cell and get cell address it is located in
    rng = FindValueInTable("12345", Date 1)
    
'   Select cell on "G-Nummern" sheet
    Range(rng).Select
    
End Sub
but that doesnt work whysoever...
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
You will need to format the date to match the format that your workbook currently uses, i.e.
rng = FindValueInTable("Sheet1", Format(Date, "d-mmm"), 1)
 

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
You will need to format the date to match the format that your workbook currently uses, i.e.
rng = FindValueInTable("Sheet1", Format(Date, "d-mmm"), 1)
worked perfectly. Thank you, again :)
 

Joe4

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

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
worked perfectly. Thank you, again :)
third times the charm :D

soooo now i have a whole new workbook where the value i search for is located. Do you think that theres a way to reference a table in a different workbook?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
soooo now i have a whole new workbook where the value i search for is located. Do you think that theres a way to reference a table in a different workbook?
I think that is a whole new question for a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
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