PDA

Vollständige Version anzeigen : "SVERWEIS"-ähnliche Suche über mehrere Blätter


archi01
11.04.2012, 10:08
Hallo,
folgendes bekomme ich nicht hin:
Ich habe ein Übersichtsblatt ganz vorne in meiner Arbeitsmappe. Alle folgenden Blätter sind ausgeblendet.
Im Übersichtsblatt soll in Zelle D18 ein Organisationszeichen (zB.: 111.A)eingegeben werden. Nun sollen alle nachfolgenden Blätter nach diesem Zeichen abgesucht werden.
Das Zeichen kann in jedem Blatt immer in den Spalten C oder K stehen. Wird das Zeichen gefunden, soll der dazugehörige Namen in Zelle D19 plus Telefonnummer in E19 ausgegeben werden. Die Namen stehen immer in Spalte D (zu Orgzeichen in C) oder in Spalte L (zu Orgzeichen in K). Die Telnummern immer noch eins weiter in E bzw. M.
Ist das per Formel möglich? Bzw wenn nicht, wäre ein Makro kompliziert? (Ich kann das auf keinen Fall, jedenfalls nicht sinnvoll :mrcool: )
Schonmal Danke und Gruß
Andreas

archi01
11.04.2012, 14:39
Ich weiß, irgendwie war das Thema schon so oft dran. Aber irgendwie bekomme ich die Antworten auch nicht auf meine Sache umgesetzt.
Kürzer als oben gesagt ist das Problem folgendes:
Ich suche den Wert aus D18 in allen nachfolgenden Blättern. Er kommt insgesamt nur einmal vor. Wird der Wert gefunden, sollen immer einmal der Wert aus der selben Zeile/nächste Spalte in D19 und der Wert aus der selben Zeile/übernächste Spalte in E19 ausgegeben werden.
Ich finde, das klingt so einfach :rolleyes: (<- was es für mich aber noch schwerer macht, die Grenzen meines Gehirnes zu akzeptieren).
Wenn die Lösung nur mit einem Makro geht, dann wäre es toll wenn das Makro immer dann startet, wenn ein Wert in D18 eingegeben und mit Return bestätigt wurde.
Gruß und vorab vielen Dank für die Hilfe,
Andreas (das ist der mit dem SVERWEISKETTENFORMELKOLLER)

chris-kaiser
11.04.2012, 14:42
Hallo Andreas

"der immer Suchende" ^^
klingt ja fast ähnlich wie die "Rundumschlagsuche" vom letzten mal. ^^

wenn du eine Demomappe mit dem gew. Ergebnis machst, kümmere ich mich darum!

Ich bin einfach zu faul um jetzt die Mappe neu zu erfinden. :grins:

archi01
11.04.2012, 14:57
Hallo chris-kaiser,
schön das Du mein indirektes Flehen erhörst ;)
Tja, das ist schade. Ich kann leider nix raus und nix rein laden hier. Das will mein Arbeitgeber nicht.
Du hast recht, vom Prinzip ähnlich. Dachte auch schon daran, deinen tollen Code zu verwenden. Aber ohjeh, nix versteh.
Evtl. könnte ich ja trotzdem auf Deine Hilfe zählen. Was brauchst Du denn für Angaben?
Das erste Blatt (wo die Werte ein- und ausgegeben werden) heißt Eingabe. Die nachfolgenden Blätter heißen 2, 3, 4, 451, 452, 453, 5, 7, 8 und 22.
Die OrgaZeichen stehen pro Blatt in den Spalten C und K, immer ab Zeile 3 bis max Zeile 110. Es sind zumeist Leerzeilen vorhanden. Die Ausgabewerte stehen dann eben in den Spalten D/E bzw L/M, wobei jeder der beiden Werte in einer eigenen Zelle (Eingabe!D19 bzw Eingabe!E19) stehen soll.
Diesmal wird das OrgaZeichen immer fest eingegeben mit der Syntax 111.A
Schonmal Danke (so oder so) und Gruß
Andreas

archi01
11.04.2012, 15:01
Habe im Laufe meiner Suche die einzelnen relevanten Bereiche auch per Namensvergabe benannt.
Wäre das noch eine Hilfe?
Gruß
Andreas

archi01
11.04.2012, 15:32
Ach herrjeh, da fällt mir auf.
SVERWEIS ist neben den ganzen anderen Gründen die dagegen sprechen ohnehin die schlechteste Idee gewesen, weil die OrgZeichen im Suchbereich ja garnicht sortiert sind und auch nicht sortiert werden können.
Gruß
Andreas

chris-kaiser
11.04.2012, 15:59
Hallo Andreas

Formellösung wird wohl schwierig

Um das sinnvoll umzusetzen, wäre eine DEMO-Mappe für mich zumindest erforderlich. (eine Mappe mit irgendwelchen Dummydaten die den selben Aufbau hat wie das Orginal)
möglicherweise kannst du ja so etwas von einem privaten PC aus anhängen.

Ich gehe jetzt erst mal in den Feierabend (bis Montag oder länger) :grins:
Vielleicht gelingt es mir zwischdurch mal kurz im Forum vorbeizuschauen....)

archi01
11.04.2012, 16:02
o.k., mach ich mal von Zuhause aus.
Schöne Feierabendwoche :winken:
Danke und Gruß
Andreas

archi01
11.04.2012, 21:45
Hallo Chris-kaiser,
hier eine rudimentäre Bspmappe. Reicht das? Im Orig sind noch mehr Blätter wie oben beschrieben, dachte aber zur Lösungsfindung könnte das reichen.
In den Blättern sind unterschiedlich viele Datenzeilen. Zum Teil durchgehend von zeile 3 bis 56, manche nur bis Zeile 10. Aber immer kommen ab Zeile 90 nochmal 2-5 Datensätze und ab Zeile 100 ebenso.
Warum? Hat sich mein Herz gegen mein Hirn durchgesetzt :-)
Wäre toll, wenn es klappen könnte. Das Original wir allerdings in XL2007 erstellt.

Gruß und Danke,
Andreas

T-K
11.04.2012, 23:59
Hallo Andreas,

Du kannst das über den folgenden Code mal versuchen. Es sollte das sein was Du willst. Wenn ich um diese Zeit alles richtig verstanden habe :D


Option Explicit

Sub suche()
Dim ws As Worksheet
Dim i As Integer
Dim strSuche As String
Dim strErg1 As String
Dim strErg2 As String
Dim bTreffer As Boolean

Application.ScreenUpdating = False
strSuche = Trim(Worksheets("Eingabe").Cells(18, 4).Value)
bTreffer = False

For Each ws In Worksheets
ws.Activate

If bTreffer = True Then 'Prüfen ob ein Treffer war
Exit For
End If
If ws.Name <> "Eingabe" Then

For i = 3 To 110

If Cells(i, 3).Value = strSuche Then

strErg1 = Cells(i, 4).Value
strErg2 = Cells(i, 5).Value
bTreffer = True
Exit For

End If

If Cells(i, 11).Value = strSuche Then

strErg1 = Cells(i, 12).Value
strErg2 = Cells(i, 13).Value

bTreffer = True
Exit For
End If
Next i

End If

Next ws

'Info falls nichts gefunden wurde

If bTreffer = False Then
MsgBox "Es konnte nichts gefunden werden"
Exit Sub
End If

With Worksheets("Eingabe")

.Cells(19, 4).Value = strErg1
.Cells(19, 5).Value = strErg2

End With

Worksheets("Eingabe").Activate
Application.ScreenUpdating = True

End Sub


Bei Fragen, einfach fragen. ;)

Viele Grüße
Thomas

archi01
12.04.2012, 04:32
Hallo T-K,
vielen Dank für die schnelle und späte Lösung :-)
Klappt bestens! Toll.
Was muss ich tun, damit der Code automatisch ausgeführt wird, wenn mann die Eingabe des Suchbegriffes in D18 mit Return bestätigt?
Gruß und schonmal vielen Dank für die Mühe,
Andreas

archi01
12.04.2012, 04:50
Hallo T-K,
habe den Code nun mit einem Button verknüpft. Klappt toll, solange das Blatt nicht geschützt ist. Dann bringt er eine Fehlermeldung. Den Button habe ich aber auf nicht gesperrt gestellt. Die Eingabezelle D18 auch. Die Blätter, in welchen gesucht wird sind komplett gesperrt und ausgeblendet.
Gruß und vielen Dank,
Andreas

chris-kaiser
12.04.2012, 07:42
Hallo Andreas

Warum sich das Leben so schwer machen.... :rolleyes:
Die Blätter, in welchen gesucht wird sind komplett gesperrt und ausgeblendet.


Welchen Grund hat das? ;) warum nicht alles in ein Blatt verfrachten... und eine ECHTE Datentabelle aufbauen? (mit EINER Überschrift Name, EINER Überschrift Tel. EINER Überschrift OrgZ usw. ) :)

Dann wird kein "biegewiedergerade" Makro benötigt so wie das folgende.
Ich habe den Code ein wenig überladen kommentiert, falls welche Änderungen zu machen wären ...
<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Option</span> <span class="TOKEN">Explicit</span> <span class="REM">'Variablen dek. erforderlich</span>
<span class="TOKEN">Private Sub</span> cmd_ok_Click() <span class="REM">'Code liegt auf einer Schaltfl&auml;cje mit dem Namen cmd_Ok</span>
<span class="TOKEN">Dim</span> wsEingabe <span class="TOKEN">As</span> Worksheet
<span class="TOKEN">Dim</span> ws <span class="TOKEN">As</span> Worksheet
<span class="TOKEN">Dim</span> strSuchbegriff <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> objFundstelle <span class="TOKEN">As</span> Object
<span class="TOKEN">Dim</span> strFirstMatch <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strName <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> bCheck <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>
<span class="TOKEN">Set</span> wsEingabe = Tabelle1 <span class="REM">'ACHTUNG in der Orginalmappe den Codenamen der Tabelle (Eingabe) &uuml;berpr&uuml;fen!</span>
strSuchbegriff = wsEingabe.Range(&quot;D18&quot;).Value <span class="REM">'SuchWert aus Zelle D19 im Blatt Eingabe</span>
&nbsp;
<span class="REM">'#################</span>
<span class="REM">''dieser Teil w&auml;re bei Workbook_open in &quot;DieseArbeitsmappe&quot; besser aufgehoben!</span>
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> ws <span class="TOKEN">In</span> Worksheets
<span class="REM"> 'hier werden deine Bl&auml;tter gesch&uuml;tzt aber VBA Zugriff ist erlaubt'Password ANPASSEN oder nicht vergeben.</span>
ws.Protect Password:=&quot;Dein_PW_oder eben kein PW&quot;, Userinterfaceonly:=True
<span class="TOKEN">Next</span>
<span class="REM">'##################</span>
&nbsp;
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> ws <span class="TOKEN">In</span> Worksheets <span class="REM">'gehe alle Bl&auml;tter durch</span>
<span class="TOKEN">If</span> ws.CodeName &lt;&gt; wsEingabe.CodeName <span class="TOKEN">Then</span> <span class="REM">'verwende alle Bl&auml;tter ausser die Eingabe</span>
<span class="TOKEN">With</span> ws.Range(&quot;C:C,K:K&quot;) <span class="REM">'Suche in den Spalte C und K</span>
<span class="TOKEN">Set</span> objFundstelle = .Find(strSuchbegriff, lookat:=xlWhole)
<span class="TOKEN">If</span> <span class="TOKEN">Not</span> objFundstelle <span class="TOKEN">Is</span> <span class="TOKEN">Nothing</span> <span class="TOKEN">Then</span> <span class="REM">'Wenn was gefunden wird</span>
strFirstMatch = objFundstelle.Address <span class="REM">'merke den ersten Treffer</span>
<span class="TOKEN">Do</span>
<span class="REM"> 'FALLS mehr als ein Treffer ist werden mehrere Namen angezeigt!</span>
<span class="REM"> 'dann die Daten &uuml;berpr&uuml;fen!!</span>
strName = strName &amp; objFundstelle(1, 2).Value
wsEingabe.Range(&quot;D19&quot;).Value = strName <span class="REM">'trage den Namen ein</span>
wsEingabe.Range(&quot;E19&quot;).Value = objFundstelle(1, 3).Value <span class="REM">'die TelNummer</span>
bCheck = <span class="TOKEN">True</span>
<span class="TOKEN">Set</span> objFundstelle = .FindNext(objFundstelle) <span class="REM">'suche weiter</span>
<span class="REM"> 'suche solange nichts mehr gefunden wird oder der erste Treffer der Letze ist</span>
<span class="TOKEN">Loop</span> <span class="TOKEN">While</span> <span class="TOKEN">Not</span> objFundstelle <span class="TOKEN">Is</span> <span class="TOKEN">Nothing</span> <span class="TOKEN">And</span> strFirstMatch &lt;&gt; objFundstelle.Address
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Set</span> objFundstelle = <span class="TOKEN">Nothing</span>
strFirstMatch = &quot;&quot;
<span class="TOKEN">Next</span>
<span class="REM">'falls gar nichts gefunden wurde.........</span>
<span class="TOKEN">If</span> <span class="TOKEN">Not</span> bCheck <span class="TOKEN">Then</span> MsgBox strSuchbegriff &amp; &quot; nicht gefunden&quot;
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>

mücke
12.04.2012, 07:59
Moin Andreas,

habe es mal mit einer Formellösung (mit Hilfszellen) versucht.
Die Berechnungszellen D15 bis E16 können überall im TB hinterlegt und ausgeblendet werden.
<b>Eingabe</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-family:Verdana; ">Name1</td><td style="font-family:Verdana; text-align:right; ">1890</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-family:Verdana; ">#NV</td><td style="font-family:Verdana; ">#NV</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D15</td><td >=SVERWEIS($D$18;INDIREKT<span style=' color:008000; '>("'"&INDEX<span style=' color:#0000ff; '>({2;3};VERGLEICH<span style=' color:#ff0000; '>(1;<span style=' color:#804000; '>(Z&Auml;HLENWENN<span style=' color:#ff7837; '>(INDIREKT<span style=' color:#8000ff; '>("'"&{2;3}&"'!C:C")</span>;$D$18)</span>&gt;0)</span>+0;0)</span>)</span>&"'!C:E")</span>;2;0)</td></tr><tr><td >E15</td><td >=SVERWEIS($D$18;INDIREKT<span style=' color:008000; '>("'"&INDEX<span style=' color:#0000ff; '>({2;3};VERGLEICH<span style=' color:#ff0000; '>(1;<span style=' color:#804000; '>(Z&Auml;HLENWENN<span style=' color:#ff7837; '>(INDIREKT<span style=' color:#8000ff; '>("'"&{2;3}&"'!C:C")</span>;$D$18)</span>&gt;0)</span>+0;0)</span>)</span>&"'!C:E")</span>;3;0)</td></tr><tr><td >D16</td><td >=SVERWEIS($D$18;INDIREKT<span style=' color:008000; '>("'"&INDEX<span style=' color:#0000ff; '>({2;3};VERGLEICH<span style=' color:#ff0000; '>(1;<span style=' color:#804000; '>(Z&Auml;HLENWENN<span style=' color:#ff7837; '>(INDIREKT<span style=' color:#8000ff; '>("'"&{2;3}&"'!K:K")</span>;$D$18)</span>&gt;0)</span>+0;0)</span>)</span>&"'!K:M")</span>;2;0)</td></tr><tr><td >E16</td><td >=SVERWEIS($D$18;INDIREKT<span style=' color:008000; '>("'"&INDEX<span style=' color:#0000ff; '>({2;3};VERGLEICH<span style=' color:#ff0000; '>(1;<span style=' color:#804000; '>(Z&Auml;HLENWENN<span style=' color:#ff7837; '>(INDIREKT<span style=' color:#8000ff; '>("'"&{2;3}&"'!K:K")</span>;$D$18)</span>&gt;0)</span>+0;0)</span>)</span>&"'!K:M")</span>;3;0)</td></tr></table></td></tr></table><br /><br /><b>Eingabe</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >231.A</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Name1</td><td style="text-align:right; ">1890</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D19</td><td >=WENN(ISTNV<span style=' color:008000; '>(D15)</span>;D16;D15)</td></tr><tr><td >E19</td><td >=WENN(ISTNV<span style=' color:008000; '>(E15)</span>;E16;E15)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

T-K
13.04.2012, 23:50
Hallo Andreas,

sorry für die späte Reaktion. Hat sich deine Frage zum dem Blattschutz erledigt?

Viele Grüße
Thomas

archi01
10.05.2012, 05:44
Hallo,
entschuldigt bitte vielmals das ich mich nicht mehr gemeldet habe. War erst krank und dann ist mir wg Arbeitsstress die Sache erstmal aus dem Blickfeld geraten.
Vielen Dank euch für die mühevolle und ausführliche Bearbeitung. Hat mir sehr geholfen. Aktueller Stand ist, dass es bestens funktioniert. Dank euch.
Toll!
Also nochmal Danke für die Mühe und Nachsicht für mein längeres Nichtmelden.
Eine angenehme Restarbeitswoche wünscht euch
Andreas