mreske
Sep 19, 2019, updated at 12:38:45 (UTC)
view1910
view12
0
Goto Top

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

GermansolvedQuestionMicrosoft OfficeMicrosoft
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ß
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 496330

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

Printed on: April 18, 2024 at 15:04 o'clock

12 Comments
Latest comment
Goto Top
Mitglied: 140913
Solution 140913 Sep 19, 2019, updated at Sep 20, 2019 at 13:42:16 (UTC)
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)
Member: mreske
mreske Sep 19, 2019 at 13:26:41 (UTC)
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
Member: eisbein
eisbein Sep 19, 2019 at 14:32:34 (UTC)
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
Solution 140913 Sep 19, 2019 updated at 15:04:56 (UTC)
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})
Member: mreske
mreske Sep 19, 2019 at 15:45:05 (UTC)
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ß
Member: mreske
mreske Sep 19, 2019 at 16:14:50 (UTC)
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
Solution 140913 Sep 19, 2019, updated at Sep 20, 2019 at 13:41:57 (UTC)
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
heart1
Member: mreske
mreske Sep 20, 2019 updated at 12:05:53 (UTC)
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
Solution 140913 Sep 20, 2019 updated at 13:49:22 (UTC)
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
Member: mreske
mreske Sep 20, 2019 updated at 14:02:48 (UTC)
Goto Top
super!!! Tausend Dank,
KALK-Verbot ist somit umgehend aufgehoben :-=)
Beste Grüße und schönes WE
Mitglied: 140913
140913 Sep 20, 2019 updated at 14:47:08 (UTC)
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.
Member: mreske
mreske Sep 28, 2019 at 07:39:13 (UTC)
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ß
GermansolvedQuestionMicrosoft OfficeMicrosoft
Hotly discussed
AlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 CommentsDaniEnd of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment