PDA

Vollständige Version anzeigen : Macro statt Sverweis


Ordon23
02.08.2012, 20:06
Hallo
Ich hab folgendes Problem, was leider nicht leicht zu beschreiben ist, bei dem ich leider nicht mehr weiter komme
Meine Arbeitsmappe auf Tabelle1 sieht folgender Maße aus
In SpalteA stehn mehrere Zahlen in SpalteB das Datum in SpalteC und D werte und in SpalteF eine Benotung.
Nun möchte ich mit einem Makro folgendes machen
Es soll Zahl in Spalte A nach einer bestimmten Zahl durchsuchen und dann in SpalteF nach einer bestimmten Note suchen.
Die funde soll es dann in Tabelle2 auflisten.
Mit der Sverweis formel hatte ich das Problem das diese immer nur den ersten fund anzeigt und nicht alle weitern.

Hoffe ihr könnt mir weiter Helfen.
Gruß Ordon23

Backowe
02.08.2012, 20:40
Hi,

probiere es mal so:

<pre><span style="font-family=Courier New,Arial; font-size=10pt"; >

<b><span style="color:#008000"; >' **************************************************************
' Modul: Modul1 Typ = Allgemeines Modul
' **************************************************************</span></b>

<span style="color:#000080"; >Option</span> <span style="color:#000080"; >Explicit</span>
<b><span style="color:#000080"; >Sub</span> MehrereSuchbegriffeFindenUndAuflisten()</b>
<span style="color:#000080"; >Dim</span> c <span style="color:#000080"; >As</span> <span style="color:#000080"; >Object</span>
<span style="color:#000080"; >Dim</span> firstAddress <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> Suchbegriff <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
Suchbegriff = InputBox(<span style="color:#800000"; >"Bitte die Zahl eingeben!"</span>)
<span style="color:#000080"; >With</span> Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Range(<span style="color:#800000"; >"a1:a40"</span>)
<span style="color:#000080"; >Set</span> c = .Find(Suchbegriff, LookIn:=xlValues)
<span style="color:#000080"; >If</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >Then</span>
firstAddress = c.Address
<span style="color:#000080"; >Do</span>
Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Range(<span style="color:#800000"; >"A"</span> & Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Cells(Rows.Count, <span style="color:#800000"; >"A"</span>).End(xlUp).Row + 1) = c.Offset(0, 5)
<span style="color:#000080"; >Set</span> c = .FindNext(c)
<span style="color:#000080"; >Loop</span> <span style="color:#000080"; >While</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >And</span> c.Address &lt;&gt; firstAddress
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span>
<b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b>
</span></pre>

Ordon23
02.08.2012, 21:13
Danke für die Schnelle antwort.
Kannst du vieleicht erklären wie sich der Code aufbaut.
Wie kann man machen das man mehrere Spalten einer Zeile anzeigt?

Gruß Ordon23

Backowe
02.08.2012, 21:24
Hi,

ich habe mal den Code etwas dokumentiert, hoffe so wird es etwas klarer!

<pre><span style="font-family=Courier New,Arial; font-size=10pt"; >

<b><span style="color:#008000"; >' **************************************************************
' Modul: Modul1 Typ = Allgemeines Modul
' **************************************************************</span></b>

<span style="color:#000080"; >Option</span> <span style="color:#000080"; >Explicit</span>
<b><span style="color:#000080"; >Sub</span> MehrereSuchbegriffeFindenUndAuflisten()</b>
<span style="color:#000080"; >Dim</span> c <span style="color:#000080"; >As</span> <span style="color:#000080"; >Object</span>
<span style="color:#000080"; >Dim</span> firstAddress <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> Suchbegriff <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
Suchbegriff = InputBox(<span style="color:#800000"; >"Bitte die Zahl eingeben!"</span>)
<span style="color:#000080"; >With</span> Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Range(<span style="color:#800000"; >"a1:a40"</span>)
<span style="color:#000080"; >Set</span> c = .Find(Suchbegriff, LookIn:=xlValues)
<span style="color:#000080"; >If</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >Then</span>
firstAddress = c.Address
<span style="color:#000080"; >Do</span>
<span style="color:#008000"; >'Sheets("Tabelle2").Range("A" & Sheets("Tabelle2").Cells(Rows.Count, "A").End(xlUp).Row + 1)&lt;-- Hier wird in Tabelle2 in Spalte A die n&auml;chste freie Zelle gesucht und beschrieben!</span>
<span style="color:#008000"; >'c.Offset(0, 5) c ist die Adresse in Tabelle1 in der Spalte A und das Offset(0,5) nimmt den Wert der um 5 Spalten weiter steht, also Spalte F in der gleichen Zeile!</span>
Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Range(<span style="color:#800000"; >"A"</span> & Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Cells(Rows.Count, <span style="color:#800000"; >"A"</span>).End(xlUp).Row + 1) = c.Offset(0, 5)
<span style="color:#000080"; >Set</span> c = .FindNext(c) <span style="color:#008000"; >'&lt;-- Suche den n&auml;chsten Treffer!</span>
<span style="color:#000080"; >Loop</span> <span style="color:#000080"; >While</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >And</span> c.Address &lt;&gt; firstAddress <span style="color:#008000"; >'Suche solange bis der Suchbegriff nicht mehr gefunden wird!</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span>
<b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b>
</span></pre>

Ordon23
02.08.2012, 22:06
Ok jetzt verstehe ich es langsam.
Jetzt hab nur noch 2 Sachen.
Gibt es auch eine möglichkeit das er bei einer bestimmten Zelle mit eintragen anfängt und den inhalt einfache überschreibt?
Und geht es auch das man in der SpalteF auch noch nach einen bestimmten begriff zum Filtern sucht?

Gruß Ordon23

Backowe
03.08.2012, 08:36
Hi,

zu Frage 1, das könnest du so lösen:

<pre><span style="font-family=Courier New,Arial; font-size=10pt"; >

<b><span style="color:#008000"; >' **************************************************************
' Modul: Modul1 Typ = Allgemeines Modul
' **************************************************************</span></b>

<span style="color:#000080"; >Option</span> <span style="color:#000080"; >Explicit</span>
<b><span style="color:#000080"; >Sub</span> MehrereSuchbegriffeFindenUndAuflisten()</b>
<span style="color:#000080"; >Dim</span> c <span style="color:#000080"; >As</span> <span style="color:#000080"; >Object</span>
<span style="color:#000080"; >Dim</span> firstAddress <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> Suchbegriff <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> lngZeile <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span>
Suchbegriff = InputBox(<span style="color:#800000"; >"Bitte die Zahl eingeben!"</span>)
<span style="color:#000080"; >With</span> Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Range(<span style="color:#800000"; >"A1:A"</span> & Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Cells(Rows.Count, <span style="color:#800000"; >"A"</span>).End(xlUp).Row)
lngZeile = 15
<span style="color:#000080"; >Set</span> c = .Find(Suchbegriff, LookIn:=xlValues)
<span style="color:#000080"; >If</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >Then</span>
firstAddress = c.Address
<span style="color:#000080"; >Do</span>
Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Range(<span style="color:#800000"; >"A"</span> & lngZeile) = c.Offset(0, 5)
lngZeile = lngZeile + 1
<span style="color:#000080"; >Set</span> c = .FindNext(c)
<span style="color:#000080"; >Loop</span> <span style="color:#000080"; >While</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >And</span> c.Address &lt;&gt; firstAddress
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span>
<b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b>
</span></pre>

zu Frage 2: Da muss ich nochmals nachfragen, mach bitte mal ein genaues Bsp. wie du das meinst. Deine Frage lässt, nach meiner Meinung, noch zu viel Interpretaionsspielraum zu!

Ordon23
03.08.2012, 09:05
Morgen

Und nochmals vielen Dank für deine Antwort.
Bei der Frage 2 meinte ich das so.
Als erstes sucht er in der SpalteA nach einen Ergebnis (z.B. 154) danach soll er in SpalteF schauen welche Note dort hinterlegt ist (z.B. Gut) und nur die mit der Gewünschten Note soll er in Tabelle2 auflisten. Heißt also hier alle Ergebnisse von 154 mit Gut.
Hoffe so ist es vertändlich.

Gruß Heiko

Backowe
03.08.2012, 09:24
Hallo Heiko,

wenn ich dich richtig verstanden habe, so:

<pre><span style="font-family=Courier New,Arial; font-size=10pt"; >

<b><span style="color:#008000"; >' **************************************************************
' Modul: Modul1 Typ = Allgemeines Modul
' **************************************************************</span></b>

<span style="color:#000080"; >Option</span> <span style="color:#000080"; >Explicit</span>
<b><span style="color:#000080"; >Sub</span> MehrereSuchbegriffeFindenUndAuflisten()</b>
<span style="color:#000080"; >Dim</span> c <span style="color:#000080"; >As</span> <span style="color:#000080"; >Object</span>
<span style="color:#000080"; >Dim</span> firstAddress <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> Suchbegriff1 <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>, Suchbegriff2 <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> lngZeile <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span>
Suchbegriff1 = InputBox(<span style="color:#800000"; >"Bitte den ersten Suchbegriff ein!"</span>)
Suchbegriff2 = InputBox(<span style="color:#800000"; >"Bitte den zweiten Suchbegriff ein!"</span>)
Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Range(<span style="color:#800000"; >"A15:A"</span> & Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Cells(Rows.Count, <span style="color:#800000"; >"A"</span>).End(xlUp).Row).ClearContents
<span style="color:#000080"; >With</span> Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Range(<span style="color:#800000"; >"A1:A"</span> & Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Cells(Rows.Count, <span style="color:#800000"; >"A"</span>).End(xlUp).Row)
lngZeile = 15
<span style="color:#000080"; >Set</span> c = .Find(Suchbegriff1, LookIn:=xlValues)
<span style="color:#000080"; >If</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >Then</span>
firstAddress = c.Address
<span style="color:#000080"; >Do</span>
<span style="color:#000080"; >If</span> c.Offset(0, 5) = Suchbegriff2 <span style="color:#000080"; >Then</span>
Sheets(<span style="color:#800000"; >"Tabelle2"</span>).Range(<span style="color:#800000"; >"A"</span> & lngZeile) = c.Offset(0, 5)
lngZeile = lngZeile + 1
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >Set</span> c = .FindNext(c)
<span style="color:#000080"; >Loop</span> <span style="color:#000080"; >While</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >And</span> c.Address &lt;&gt; firstAddress
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span>
<b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b>
</span></pre>

Ordon23
05.08.2012, 17:42
Das ist Genial.
Habe heute ein bisschen damit schon getestet und verstehe das System immer besser :grins:
So sieht es momentan aus
Option Explicit
Sub MehrereSuchbegriffeFindenUndAuflisten2()
Dim c As Object
Dim firstAddress As String
Dim Suchbegriff1 As String, Suchbegriff2 As String
Dim lngZeile As Long
Suchbegriff1 = ("148")
Suchbegriff2 = ("gut")
Sheets("roxa").Range("C14:C" & Sheets("roxa").Cells(Rows.Count, "C").End(xlUp).Row).ClearContents
With Sheets("Tabelle1").Range("B1:B" & Sheets("Tabelle1").Cells(Rows.Count, "B").End(xlUp).Row)
lngZeile = 14
Set c = .Find(Suchbegriff1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 5) = Suchbegriff2 Then
Sheets("roxa").Range("C" & lngZeile) = c.Offset(0, 5)
Sheets("roxa").Range("D" & lngZeile) = c.Offset(0, 4)
Sheets("roxa").Range("E" & lngZeile) = c.Offset(0, 6)
Sheets("roxa").Range("J" & lngZeile) = c.Offset(0, 3)
Sheets("roxa").Range("K" & lngZeile) = c.Offset(0, 2)
lngZeile = lngZeile + 1
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Und nun hab ich schon wierder 2 Fragen:
1. geht es das das Makro einen zweiten suchbegriff von ("148") sucht an einer anderen stelle Auflistetet. z.B: "schlecht" in spalte M14? oder muss ich immer ein neues Makro laufen lassen.
2. Kann man auch zwei Suchbegriffe gleichzeitig such lassen? Also z.B. Suchbegriff2 = ("gut" + "sehrgut") hat so nicht funktioniert :( wäre ja auch zu einfach gewesen.

Gruß Heiko

josef e
05.08.2012, 18:35
<div style="width:98%; margin-left:5px; margin-right:15px; text-align:justify;">
Hallo Heiko,

deine VBA-Lösung ist ja schon fortgeschritten, aber dennoch hier eine Formellösung.

<b>Tabelle3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Tahoma,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;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:86px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:109px;" /><col style="width:80px;" /><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 >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Nummer</td><td style="font-weight:bold; ">Datum</td><td style="font-weight:bold; ">Wert 1</td><td style="font-weight:bold; ">Wert 2</td><td style="font-weight:bold; ">Note</td><td style="font-weight:bold; ">&nbsp;</td><td style="font-weight:bold; ">&nbsp;</td><td style="font-weight:bold; ">&nbsp;</td><td style="font-weight:bold; ">Nummer</td><td style="font-weight:bold; ">Note(n)</td><td style="font-weight:bold; ">&nbsp;</td><td style="font-weight:bold; ">Datum</td><td style="font-weight:bold; ">Wert 1</td><td style="font-weight:bold; ">Wert 2</td><td style="font-weight:bold; ">Note</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">150</td><td style="text-align:right; ">13.07.2012</td><td style="text-align:right; ">45</td><td style="text-align:right; ">44</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">151</td><td >Sehr Gut</td><td >&nbsp;</td><td style="text-align:right; ">14.07.2012</td><td style="text-align:right; ">48</td><td style="text-align:right; ">27</td><td >Sehr Gut</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">151</td><td style="text-align:right; ">14.07.2012</td><td style="text-align:right; ">48</td><td style="text-align:right; ">27</td><td >Sehr Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >Gut</td><td >&nbsp;</td><td style="text-align:right; ">12.07.2012</td><td style="text-align:right; ">30</td><td style="text-align:right; ">14</td><td >Gut</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">150</td><td style="text-align:right; ">20.07.2012</td><td style="text-align:right; ">49</td><td style="text-align:right; ">42</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">17.07.2012</td><td style="text-align:right; ">30</td><td style="text-align:right; ">23</td><td >Gut</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">153</td><td style="text-align:right; ">15.07.2012</td><td style="text-align:right; ">40</td><td style="text-align:right; ">39</td><td >Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">160</td><td style="text-align:right; ">12.07.2012</td><td style="text-align:right; ">16</td><td style="text-align:right; ">18</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">155</td><td style="text-align:right; ">11.07.2012</td><td style="text-align:right; ">30</td><td style="text-align:right; ">50</td><td >Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">151</td><td style="text-align:right; ">13.07.2012</td><td style="text-align:right; ">24</td><td style="text-align:right; ">42</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">150</td><td style="text-align:right; ">13.07.2012</td><td style="text-align:right; ">40</td><td style="text-align:right; ">37</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">151</td><td style="text-align:right; ">15.07.2012</td><td style="text-align:right; ">18</td><td style="text-align:right; ">31</td><td >Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">153</td><td style="text-align:right; ">16.07.2012</td><td style="text-align:right; ">13</td><td style="text-align:right; ">30</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">151</td><td style="text-align:right; ">14.07.2012</td><td style="text-align:right; ">12</td><td style="text-align:right; ">28</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">152</td><td style="text-align:right; ">11.07.2012</td><td style="text-align:right; ">14</td><td style="text-align:right; ">42</td><td >Sehr Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">160</td><td style="text-align:right; ">13.07.2012</td><td style="text-align:right; ">47</td><td style="text-align:right; ">50</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">160</td><td style="text-align:right; ">16.07.2012</td><td style="text-align:right; ">19</td><td style="text-align:right; ">15</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">159</td><td style="text-align:right; ">19.07.2012</td><td style="text-align:right; ">13</td><td style="text-align:right; ">31</td><td >Sehr Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">159</td><td style="text-align:right; ">20.07.2012</td><td style="text-align:right; ">38</td><td style="text-align:right; ">30</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">152</td><td style="text-align:right; ">13.07.2012</td><td style="text-align:right; ">15</td><td style="text-align:right; ">19</td><td >Sehr Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">157</td><td style="text-align:right; ">18.07.2012</td><td style="text-align:right; ">28</td><td style="text-align:right; ">15</td><td >Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">151</td><td style="text-align:right; ">12.07.2012</td><td style="text-align:right; ">30</td><td style="text-align:right; ">14</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">159</td><td style="text-align:right; ">11.07.2012</td><td style="text-align:right; ">41</td><td style="text-align:right; ">24</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">158</td><td style="text-align:right; ">21.07.2012</td><td style="text-align:right; ">30</td><td style="text-align:right; ">35</td><td >Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">151</td><td style="text-align:right; ">17.07.2012</td><td style="text-align:right; ">30</td><td style="text-align:right; ">23</td><td >Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">153</td><td style="text-align:right; ">15.07.2012</td><td style="text-align:right; ">43</td><td style="text-align:right; ">46</td><td >Sehr Gut</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">150</td><td style="text-align:right; ">21.07.2012</td><td style="text-align:right; ">15</td><td style="text-align:right; ">41</td><td >Sehr Schlecht</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</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 >L2</td><td >{=WENN(ZEILE<span style=' color:008000; '>(A1)</span>&gt;SUMMENPRODUKT<span style=' color:008000; '>(<span style=' color:#0000ff; '>($A$2:$A$25=$I$2)</span>*ISTZAHL<span style=' color:#0000ff; '>(VERGLEICH<span style=' color:#ff0000; '>($E$2:$E$25;$J$2:$J$3;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>($B$2:$B$25+ZEILE<span style=' color:#0000ff; '>($1:$24)</span>*10^-6;KKLEINSTE<span style=' color:#0000ff; '>(WENN<span style=' color:#ff0000; '>(<span style=' color:#804000; '>($A$2:$A$25=$I$2)</span>*ISTZAHL<span style=' color:#804000; '>(VERGLEICH<span style=' color:#ff7837; '>($E$2:$E$25;$J$2:$J$3;0)</span>)</span>;ZEILE<span style=' color:#804000; '>($1:$24)</span>)</span>;ZEILE<span style=' color:#ff0000; '>(A1)</span>)</span>)</span>)}</td></tr><tr><td >M2</td><td >{=WENN($L2="";"";INDEX<span style=' color:008000; '>(C$2:C$25;VERGLEICH<span style=' color:#0000ff; '>($L2;$B$2:$B$25+ZEILE<span style=' color:#ff0000; '>($1:$24)</span>*10^-6;0)</span>)</span>)}</td></tr><tr><td >N2</td><td >{=WENN($L2="";"";INDEX<span style=' color:008000; '>(D$2:D$25;VERGLEICH<span style=' color:#0000ff; '>($L2;$B$2:$B$25+ZEILE<span style=' color:#ff0000; '>($1:$24)</span>*10^-6;0)</span>)</span>)}</td></tr><tr><td >O2</td><td >{=WENN($L2="";"";INDEX<span style=' color:008000; '>(E$2:E$25;VERGLEICH<span style=' color:#0000ff; '>($L2;$B$2:$B$25+ZEILE<span style=' color:#ff0000; '>($1:$24)</span>*10^-6;0)</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></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>




</div>

<div style="width:100px; text-align:center; color:white; font-style:italic; font-size:0.8em; font-family:Tahoma; background-color:royalblue;margin-left:5px; margin-top:15px; padding:4px; border:3px double darkblue;">&laquo; Gru&szlig; Sepp &raquo;</div>

Backowe
05.08.2012, 21:54
Hallo Heiko,

wenn ich dich richtig verstanden habe, könntest du es so lösen:

<pre><span style="font-family=Courier New,Arial; font-size=10pt"; >

<b><span style="color:#008000"; >' **************************************************************
' Modul: Modul1 Typ = Allgemeines Modul
' **************************************************************</span></b>

<span style="color:#000080"; >Option</span> <span style="color:#000080"; >Explicit</span>
<b><span style="color:#000080"; >Sub</span> MehrereSuchbegriffeFindenUndAuflisten()</b>
<span style="color:#000080"; >Dim</span> c <span style="color:#000080"; >As</span> <span style="color:#000080"; >Object</span>
<span style="color:#000080"; >Dim</span> firstAddress <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> Suchbegriff1 <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>, Suchbegriff2 <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>, Suchbegriff3 <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> lngZeile <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span>
Suchbegriff1 = <span style="color:#800000"; >"148"</span>
Suchbegriff2 = <span style="color:#800000"; >"gut"</span>
Suchbegriff3 = <span style="color:#800000"; >"Sehr gut"</span>
Sheets(<span style="color:#800000"; >"roxa"</span>).Range(<span style="color:#800000"; >"C14:C"</span> & Sheets(<span style="color:#800000"; >"roxa"</span>).Cells(Rows.Count, <span style="color:#800000"; >"C"</span>).End(xlUp).Row).ClearContents
<span style="color:#000080"; >With</span> Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Range(<span style="color:#800000"; >"B1:B"</span> & Sheets(<span style="color:#800000"; >"Tabelle1"</span>).Cells(Rows.Count, <span style="color:#800000"; >"B"</span>).End(xlUp).Row)
lngZeile = 14
<span style="color:#000080"; >Set</span> c = .Find(Suchbegriff1, LookIn:=xlValues)
<span style="color:#000080"; >If</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >Then</span>
firstAddress = c.Address
<span style="color:#000080"; >Do</span>
<span style="color:#000080"; >If</span> Suchbegriff2 = <span style="color:#800000"; >"schlecht"</span> <span style="color:#000080"; >Then</span>
<span style="color:#008000"; >'nach was du suchen m&ouml;chtest</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >If</span> c.Offset(0, 5) = Suchbegriff2 <span style="color:#000080"; >Or</span> c.Offset(0, 5) = Suchbegriff3 <span style="color:#000080"; >Then</span>
Sheets(<span style="color:#800000"; >"roxa"</span>).Range(<span style="color:#800000"; >"C"</span> & lngZeile) = c.Offset(0, 5)
Sheets(<span style="color:#800000"; >"roxa"</span>).Range(<span style="color:#800000"; >"D"</span> & lngZeile) = c.Offset(0, 4)
Sheets(<span style="color:#800000"; >"roxa"</span>).Range(<span style="color:#800000"; >"E"</span> & lngZeile) = c.Offset(0, 6)
Sheets(<span style="color:#800000"; >"roxa"</span>).Range(<span style="color:#800000"; >"J"</span> & lngZeile) = c.Offset(0, 3)
Sheets(<span style="color:#800000"; >"roxa"</span>).Range(<span style="color:#800000"; >"K"</span> & lngZeile) = c.Offset(0, 2)
lngZeile = lngZeile + 1
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >Set</span> c = .FindNext(c)
<span style="color:#000080"; >Loop</span> <span style="color:#000080"; >While</span> <span style="color:#000080"; >Not</span> c <span style="color:#000080"; >Is</span> <span style="color:#000080"; >Nothing</span> <span style="color:#000080"; >And</span> c.Address &lt;&gt; firstAddress
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span>
<b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b>
</span></pre>

Ordon23
08.08.2012, 19:47
Vielen Dank!!!

War genau das was ich gesucht habe, funktioniert einwandfrei!

Gruß Heiko

MisterXWh
23.08.2012, 09:33
Hallo Leute,

kann mir jemand helfen?

Ich durchlaufe eine Excel Zeile für Zeile und will per Sverweis in den folgenden Zeilen die evtl. möglichen Dubletten finden:

Meine Formel im Excel ist:
=WENN(ISTFEHLER(SVERWEIS(B9;B10:$C$40;2;FALSCH));"-";"D")

Allerdings muss ich die dynamisieren. Also je Zeile wo ich gerade bin muss ich ja weiter unten suchen als aktueller Zeilenzähler + 1.

Dann soll das Ergebnis nicht in eine Excel-Zelle zurück geliefert werden sondern direkt in eine Variable geschrieben werden, damit ich diese dann abfragen kann.
Ziel muss sein, wenn eine Dublette gefunden wird, soll eine Fehlermeldung ausgegeben werden.

Ich habe das jetzt mit
erdub = WorksheetFunction.VLookup(Cells(i, 2), Sheets(1).Columns("B:C"), 2, False)
oder
Cells(9, 7) = Application.WorksheetFunction.VLookup(Sheets(1).Cells(i, 2), Range("B10:C40"), 2, False)

aber nichts hat bisher so dynamisch funktioniert.

Ich hoffe auf eure Hilfe.

Danke.

Backowe
23.08.2012, 11:09
Hi,

schau dir mal den Link an: http://www.excelformeln.de/formeln.html?welcher=185, ob dies nicht besser geeignet ist als der sverweis.