2

I'm looking for a solution to create a search function based on two dynamic values/criteria. I have a userform, with a combobox and a textbox.

Criteria 1 is coming from a combobox and should be found in (Column Building Material). Criteria 2 is a life cycle phase and can be a least one or more of the following terms: "A1-A3, A4, C1,C2,C3,C4,D". It can be found in (Column Modul)

The function of the code should be to find a global warming potential (GWP) value (see green box in data table), depending on the building material and life cycle phase. Once this value is found, it should be filled value into a textbox within the userform.

screenshot of my data table and an example:

example data table

Let's say the building material "Argeton" is chosen from the combobox. This material has multiple entries and different phases where global warming potential is caused. Now I want to know the GWP Value for e.g. the phase "A4" of "Argeton"

So far my idea was to use an if and then statement:

Dim zelle As String
Dim zelleGWPA4 As Range
Set zelle = Columns(3).Find(what:=cmb_Datensatz.Text, lookat:=xlWhole)
Set zelleGWPA4 = Columns(11).Find(what:="A4", lookat:=xlWhole)

If zelle = cmb_Datensatz.value Then zelle.Offset(0, 8).Select
....

At this point, I don't know how to go on. Since I only detected now the orange cell (see data table) to this point my thought was to search down and look for the term "A4", while the building material is still 'Agreton". Once both criteria are true, go offset(0,1) and fill value of this cell in txt_GWP_A4.

Maybe there are better and easier ways to do this. I'm open to any other ideas and thankful for any help!

Community
  • 1
  • 1
Sebth
  • 21
  • 2
  • If you data already has filters on it then filtering Building material and Modul and looking for visible cells in Column GWP could be easier. Check out [Range.Autofilter](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-autofilter-method-excel) – BerticusMaximus Oct 03 '17 at 17:37
  • Thank You. This was very useful. The syntax works now. However, since I want to show the detected values for each life cycle phase (7) and environmental impact categories (6), the code (Rang.Autofilter) is running 42 times. Speed is getting slowly then. Any other ideas? – Sebth Oct 03 '17 at 19:03
  • Have you already tested with `Application.ScreenUpdating = False` and `Application.Calculation = xlCalculationManual`? If not be sure to turn them back on [see here](https://stackoverflow.com/a/12405808/5994620) for more info. – BerticusMaximus Oct 03 '17 at 19:25

1 Answers1

0

yo can try:

  • insert a new column in datatable called 'SearchIndex'
  • the value of column SearchIndex will be a string concatenating the values of the criteria columns you want to search by, separated by semicolon or pipe or space...
  • Set zelle = Columns(SearchIndex Column).Find(what:=cmb_Datensatz.Text & "|A4", lookat:=xlWhole)

Data example in image.data example

... possibily you'd like to work with 'worksheet.listobjects'. this could help you to work with column references.

Oscar Martinez Gonzalez
  • 26
  • 1