mreske
Goto Top

Wert im nicht zusammenhängendem Bereich finden und Zeilen-Nummer ausgeben

Hallo,

ich habe eine Frage und hoffe, dass mir hier jemand weiterhelfen kann:

Name: Bereich1
Bezieht sich auf: =Tabelle1!$B$5;Tabelle1!$B$7;Tabelle1!$B$10;Tabelle1!$B$14

Der Bereich bildet sich also aus NICHT-Zusammenhängenden Zellen

Nun möchte ich innerhalb vom Bereich1
- einen bestimmten Wert suchen
- und mir die Zeilennummer anzeigen lassen, in der der Wert steht.

Hier ein Beispiel:
B2: Eingabe des gesuchten Wertes
B1: Ausgabe der Zeilennummer

2019-09-19_143811

Danke
Gruß

Content-Key: 496330

Url: https://administrator.de/contentid/496330

Ausgedruckt am: 19.03.2024 um 05:03 Uhr

Mitglied: 140913
Lösung 140913 19.09.2019, aktualisiert am 20.09.2019 um 15:42:16 Uhr
Goto Top
In ein Modul packen
Public Function SearchRange(rangeSearch As String, subject As String)
    Set f = Range(rangeSearch).Find(subject,LookIn:=xlValues)
    If Not f Is Nothing Then
        SearchRange = f.Row
    End If
End Function
und in Zelle A2 die Formel schreiben
=SearchRange("B5,B7,B10,B14";B2)
Mitglied: mreske
mreske 19.09.2019 um 15:26:41 Uhr
Goto Top
Hallo psuser,

vielen Dank. Das klappt schon mal sehr gut.
Allerdings hatte ich gehofft, dass es eine Formel als Lösung gibt.
Falls nicht, geht es natürlich auch mit dem Makro.

Danke
Mitglied: eisbein
eisbein 19.09.2019 um 16:32:34 Uhr
Goto Top
Hallo!

Natürlich würde das als Formel auch gehen. Ist dann aber grausam zu warten und sehr unflexibel.

Eine Möglichkeit wäre dann zB. alles in eine verschachtelte WENN-Formel in Zelle A2 zu packen.
=WENN(Tabelle1!$B$5=B2;5;WENN(Tabelle1!$B$7=B2;7;WENN(Tabelle1!$B$10=B2;10;WENN(Tabelle1!$B$14=B2;14,""))))  

Gruß
eisbein
Mitglied: 140913
Lösung 140913 19.09.2019 aktualisiert um 17:04:56 Uhr
Goto Top
Zitat von @mreske:
Allerdings hatte ich gehofft, dass es eine Formel als Lösung gibt.
Ist doch eine Formel face-wink

Ansonsten ginge mit deinem Beispiel auch
=SUMMENPRODUKT(ZÄHLENWENN(INDIREKT({"B5";"B7";"B10";"B14"});$B$2);{5;7;10;14})  
Mitglied: mreske
mreske 19.09.2019 um 17:45:05 Uhr
Goto Top
Hallo psuser,

vielen Dank für die beiden Lösungen.
Die VBA Version ist die elegantere Lösung.
Besten dank dafür.

Gruß
Mitglied: mreske
mreske 19.09.2019 um 18:14:50 Uhr
Goto Top
Hallo psuser,
eine Frage hätte ich noch:
Kann ich die Funktion auch über ein Makro bzw. per Schaltfläche starten?
Danke
Mitglied: 140913
Lösung 140913 19.09.2019, aktualisiert am 20.09.2019 um 15:41:57 Uhr
Goto Top
Zitat von @mreske:

Hallo psuser,
eine Frage hätte ich noch:
Kann ich die Funktion auch über ein Makro bzw. per Schaltfläche starten?
Ja klar
Public Function SearchRange(rangeSearch As String, subject As String)
    Set f = Range(rangeSearch).Find(subject,LookIn:=xlValues)
    If Not f Is Nothing Then
        SearchRange = f.Row
    End If
End Function

Sub NutzeDieFunktion()
    Result = SearchRange("B5,B7,B10,B14",ActiveSheet.Range("B2").Text)  
    ActiveSheet.Range("A2").Value = Result  
End Sub
Mitglied: mreske
mreske 20.09.2019 aktualisiert um 14:05:53 Uhr
Goto Top
Hallo psuser,

ich habe die Tabelle, die Funktion und das Makro jetzt so ausgebaut, dass mir für 6 Ränge die jeweilige Zeilenzahl angezeigt werden soll.
Das Problem ist jetzt, dass mir immer nur die Zeile vom Rang 1 angezeigt wird, wenn die Daten in Tabelle "Kalk" per Formel geholt werden.
Wenn ich die Ränge in "Kalk" überschreibe, funktioniert das Makro.
Was mache ich hier falsch?
Kann man hier eigentlich Excel-Tabellen hochladen?

Public Function SearchRange(rangeSearch As String, subject As String)
    Set f = Range(rangeSearch).Find(subject)
    If Not f Is Nothing Then
        SearchRange = f.Row
    End If
End Function

Sub NutzeDieFunktion()
Result = SearchRange("RangBereich1", 1)   
Sheets("AuswDetail").Range("Rang1").Value = Result  

Result = SearchRange("RangBereich1", 2)  
Sheets("AuswDetail").Range("Rang2").Value = Result  

Result = SearchRange("RangBereich1", 3)  
Sheets("AuswDetail").Range("Rang3").Value = Result  

Result = SearchRange("RangBereich1", 4)  
Sheets("AuswDetail").Range("Rang4").Value = Result  

Result = SearchRange("RangBereich1", 5)  
Sheets("AuswDetail").Range("Rang5").Value = Result  

Result = SearchRange("RangBereich1", 6)  
Sheets("AuswDetail").Range("Rang6").Value = Result  

End Sub

2019-09-20_131551
2019-09-20_131616

So sieht das Ergebnis aus, wenn ich die Werte in Kalk überschreibe
2019-09-20_132911
Gruß
Mitglied: 140913
Lösung 140913 20.09.2019 aktualisiert um 15:49:22 Uhr
Goto Top
wenn die Daten in Tabelle "Kalk" per Formel geholt werden
Darf ich ned, alda ich hab "Köln-Kalk" Verbotface-smile.

Wenn du in Ergebnissen von Formeln suchst musst du das der Find-Funktion auch sagen
Zeile 2 den entsprechenden Parameter hinzufügen:
Set f = Range(rangeSearch).Find(subject, LookIn:=xlValues)
Range.Find-Methode (Excel)
https://docs.microsoft.com/de-de/office/vba/api/excel.xlfindlookin
Mitglied: mreske
mreske 20.09.2019 aktualisiert um 16:02:48 Uhr
Goto Top
super!!! Tausend Dank,
KALK-Verbot ist somit umgehend aufgehoben :-=)
Beste Grüße und schönes WE
Mitglied: 140913
140913 20.09.2019 aktualisiert um 16:47:08 Uhr
Goto Top
Zitat von @mreske:
KALK-Verbot ist somit umgehend aufgehoben :-=)
Yess, endlich wieder Ramsdorfer Kölsch jipijehhhh!
Beste Grüße und schönes WE
Dito.
Mitglied: mreske
mreske 28.09.2019 um 09:39:13 Uhr
Goto Top
Mit einer Hilfsspalte ginge es auch so:

=ZEILE(INDEX(C:D;VERGLEICH(B1&B2;C:C&D:D;0);0))
dann mit Strg+Umschalt+Enter

2019-09-28_093356

Gruß