PDA

Vollständige Version anzeigen : werte aus spalte suchen


bennyboy
20.03.2009, 12:45
hallo leute,

habe schon wieder eine frage:

ich möchte aus einer spalte einen bestimmten wert suchen (FALSCH, ergebnis von Kontrollkästchen inaktiv) und dann soll er mir die Namen der Spalte links daneben einfügen.

um es genauer zu machen hier ein beispiel:

so schaut die tabelle aus:

Müller FALSCH
Maier WAHR
Huber FALSCH

jetzt möchte ich für jeden Namen der FALSCH als Ergebnis hat, diese in je extra Zelle untereinander schreiben. Das soll dann so aussehen:

Müller
Huber

hoffe ihr versteht mich!

wäre super wenn mir einer helfen könnte!

Benny

pefeu
20.03.2009, 13:19
Hallo Benny,

so sollte das funktionieren:
<FONT FACE="Arial,FixedSys"Size=2>
<Blockquote>
<FONT COLOR=#0000FF>Option Explicit</FONT>

<FONT COLOR=#0000FF>Public Sub</FONT>&nbsp;SuchenKopieren()

<FONT COLOR=#0000FF>Dim</FONT>&nbsp;WkSh_Q&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>As</FONT>&nbsp;Worksheet
<FONT COLOR=#0000FF>Dim</FONT>&nbsp;WkSh_Z&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>As</FONT>&nbsp;Worksheet
<FONT COLOR=#0000FF>Dim</FONT>&nbsp;rZelle&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>As</FONT>&nbsp;Range
<FONT COLOR=#0000FF>Dim</FONT>&nbsp;sFundst&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>As String</FONT>
<FONT COLOR=#0000FF>Dim</FONT>&nbsp;bSuchbegriff&nbsp;&nbsp;<FONT COLOR=#0000FF>As Boolean</FONT>
<FONT COLOR=#0000FF>Dim</FONT>&nbsp;lZeile_Z&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>As Long</FONT>

&nbsp;&nbsp;&nbsp;bSuchbegriff&nbsp;=&nbsp;<FONT COLOR=#0000FF>False</FONT>

&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Set</FONT>&nbsp;WkSh_Q&nbsp;=&nbsp;ThisWorkbook.Worksheets("Tabelle1")
&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Set</FONT>&nbsp;WkSh_Z&nbsp;=&nbsp;ThisWorkbook.Worksheets("Tabelle2")

&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>With</FONT>&nbsp;WkSh_Q.Columns(2)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Set</FONT>&nbsp;rZelle&nbsp;=&nbsp;.Find(What:=bSuchbegriff,&nbsp;LookAt:=xlPart,&nbsp;LookIn:=xlValues)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>If Not</FONT>&nbsp;rZelle&nbsp;<FONT COLOR=#0000FF>Is Nothing Then</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sFundst&nbsp;=&nbsp;rZelle.Address
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Do</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lZeile_Z&nbsp;=&nbsp;lZeile_Z&nbsp;+&nbsp;1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WkSh_Z.Range("A"&nbsp;&amp;&nbsp;lZeile_Z).Value&nbsp;=&nbsp;WkSh_Q.Range("A"&nbsp;&amp;&nbsp;rZelle.Row).Value
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Set</FONT>&nbsp;rZelle&nbsp;=&nbsp;.FindNext(rZelle)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Loop While Not</FONT>&nbsp;rZelle&nbsp;<FONT COLOR=#0000FF>Is Nothing And</FONT>&nbsp;rZelle.Address&nbsp;&lt;&gt;&nbsp;sFundst
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Else</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox&nbsp;"Der&nbsp;Begriff&nbsp;&nbsp;"""&nbsp;&amp;&nbsp;bSuchbegriff&nbsp;&amp;&nbsp;"""&nbsp;&nbsp;wurde&nbsp;nicht&nbsp;gefunden.",&nbsp;_
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;48,&nbsp;"&nbsp;&nbsp;&nbsp;Hinweis&nbsp;f&uuml;r&nbsp;"&nbsp;&amp;&nbsp;Application.UserName
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>End If</FONT>
&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>End With</FONT>

<FONT COLOR=#0000FF>End Sub</FONT>&nbsp;

</Blockquote>
<FONT FACE="Courier New,FixedSys"Size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Code eingefügt mit <b><a href="http://rtsoftwaredevelopment.de" target="_blank">Syntaxhighlighter 4.4</a></b></FONT>
<FONT FACE="Arial,FixedSys"Size=2>
Gruß Peter

bennyboy
20.03.2009, 14:05
öhm wow,

aber sorry ich bin kein programmierkönig...

wo soll ich das jetzt einbauen?

gibt es denn keine lösung ohne vba über ne formel oder so`???

pefeu
20.03.2009, 16:07
Hallo Benny,

mit alt + F11 gehst du in die VBA-Umgebung
dort klickst du auf Einfügen - Modul
In das Fenster des Moduls kopierst du das Makro
dann gehst du mit dem Schließen-Kreuz wieder zurück
Nun kannst du entweder über alt + F8 oder über Extras - Makro - Makros das Makro starten.

In der angehängten Mappe betrifft dein Problem Tabelle1/Tabelle2.
Die anderen Blätter kannst du ignorieren.

Gruß Peter

bennyboy
20.03.2009, 21:01
hi pefeu,

echt sau lieb deine hilfe aber ich hab mit vba 0 erfahrung und bring es auch nicht fertig das makro jetzt auf meine datei umzuschreiben...

sorry...

eine andere lösung ohne vba gibt es nicht?
ansonsten vielleicht könntest du mir ja das makro nochmal auf meine tabellen anpassen...

Tabellenblatt1 heisst "Anwesenheitsplan"
Tabellenblatt2 heisst "Einsatzplan"

suchen muss er in "Anwesenheitsplan" von a48 - b94 und dann halt überall wo bei spalte b FALSCH steht (ergebnis aus Kontrollkästchen/Zellbeziehung) soll er im "Einsatzplan" bei b41 anfangen die namen einzufügen und dann eben wie du ja schon gezeigt hast ein name pro Zelle nach unter b42, b43 usw...

vorsicht: als ich dein makro bei mir eingefügt habe kam immer eine meldung :

der begriff Falsch wurde nicht gefunden!!!

wäre echt sau cool wenn du es für mich dementsprechend anpassen könntest. für dich ist das ein klaks und ich verzweifle :-)

danke danke

Benny

bennyboy
20.03.2009, 22:15
Public Sub SuchenKopieren()

Dim WkSh_Q As Worksheet
Dim WkSh_Z As Worksheet
Dim rZelle As Range
Dim sFundst As String
Dim Suchbegriff As Boolean
Dim lZeile_Z As Long

Suchbegriff = False

Set WkSh_Q = ThisWorkbook.Worksheets("Tabelle1")
Set WkSh_Z = ThisWorkbook.Worksheets("Tabelle2")

With WkSh_Q.Columns(2)
Set rZelle = .Find(What:=Suchbegriff, LookAt:=xlPart, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
lZeile_Z = lZeile_Z + 1
WkSh_Z.Range("C" & lZeile_Z).Value = WkSh_Q.Range("C" & rZelle.Row).Value
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address <> sFundst
Else
MsgBox "Der Begriff """ & Suchbegriff & """ wurde nicht gefunden.", _
48, " Hinweis für " & Application.UserName
End If
End With

End Sub

kann mir bitte jemand sagen wo hier im code die anweisung steht in welcher spalte er nach "false" sucht????

es müsste Spalte B sein. Ich will es aber abändern.....weiss jemand bescheid??

Danke

BoskoBiati
20.03.2009, 22:26
Hallo Benny,

ohne das ich mich auskenne, tippe ich mal auf diese Zeile:

WkSh_Z.Range("C" & lZeile_Z).Value = WkSh_Q.Range("C" & rZelle.Row).Value

jinx
20.03.2009, 22:37
Moin, bennyboy,

WkSh_Q.Columns(2)
Die Angabe erfolgt nach numerischen Werten: 2 für B, 5 für E etc. Dies kann durch die Verwendung der Tabellenfunktion =Spalte() oder durch Umschalten unter Extras/Optionen auf Z1S1-Anzeige herausgefunden werden (oder aber durch VBA ;)).

bennyboy
21.03.2009, 07:40
hi jinx

danke für die Hilfe, aber vielleicht kannst du mir jetzt auch noch sagen, wo im code festgelegt wird, in welche Tabelle/Spalte/Zeile es hinkopiert werden soll.

wenn du mir das noch sagen könntest wäre ich absolut zufrieden!!!

Wäre über eine Antwort sehr dankbar

Benny

jinx
21.03.2009, 07:56
Moin, benny,

dafür ist die folgende Zeile zuständig:
WkSh_Z.Range("C" & lZeile_Z).Value = WkSh_Q.Range("C" & rZelle.Row).Value
Vielleicht wird es mit der Verwendung von Konstanten etwas anschaulicher:Public Sub SuchenKopieren()

Dim WkSh_Q As Worksheet
Dim WkSh_Z As Worksheet
Dim rZelle As Range
Dim sFundst As String
Dim Suchbegriff As Boolean
Dim lZeile_Z As Long

Const clngSPALTE As Long = 2 'Konstante der Suchspalte, hier Spalte B
Const clngFUNDSP As Long = 3 'Angabe der Spalte des Fundes, hier Spalte C
Const clngZIELSP As Long = 3 'Angabe der Spalte des Ziels, hier Spalte C

Suchbegriff = False

Set WkSh_Q = ThisWorkbook.Worksheets("Tabelle1")
Set WkSh_Z = ThisWorkbook.Worksheets("Tabelle2")

With WkSh_Q.Columns(clngSPALTE)
Set rZelle = .Find(What:=Suchbegriff, LookAt:=xlPart, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
lZeile_Z = lZeile_Z + 1
WkSh_Z.Cells(lZeile_Z, clngZIELSP).Value = WkSh_Q.Cells(rZelle.Row, clngFUNDSP).Value
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address <> sFundst
Else
MsgBox "Der Begriff """ & Suchbegriff & """ wurde nicht gefunden.", _
48, " Hinweis für " & Application.UserName
End If
End With

End Sub

bennyboy
21.03.2009, 08:13
Moin, benny,

dafür ist die folgende Zeile zuständig:

Code:

WkSh_Z.Range("C" & lZeile_Z).Value = WkSh_Q.Range("C" & rZelle.Row).Value




Aller letzte Frage jetzt: bitte zeig mir doch mal jetzt in dem codeabschnitt wie ich jetzt festlege das er jetzt in zb. die spalte 4 Zeile 10 kopieren soll?

bei mir kopiert er im Tabellenblatt 2 immer in die selbe spalte wie in Tabellenblatt 1 er soll es aber irgendwo hinkopieren wie ich es festlege wo lege ich es jetzt genau fest...

bitte hilf mir noch das eine mal ;-)

Benny

jinx
21.03.2009, 08:25
Moin, benny,

...diese in je extra Zelle untereinander schreiben
er soll es aber irgendwo hinkopieren wie ich es festlege
Der Code macht genau das, was im Eröffnungsthread gefordert wurde: er schreibt die Fundstellen untereinander in Spalte C, beginnend in Zeile 1.

Da ich mit Konstanten gearbeitet habe, können diese eingesetzt werden, um sowohl Spalte der Fundstelle als auch Spalte der Ausgabe zu verändern. Die veränderte Zeile der Ausgabe legt man dann durch einen Startwert für die Variable lZeile_Z fest, die in jedem Durchlauf um 1 erhöht wird. Wird wie hier kein Startwert vorgegeben, ist dieser 0, die erste Ausgabe erfolgt dann in Zeile 1. Hier wird wunschgemäß die Ausgabe in Zeile 10 starten:

Public Sub SuchenKopieren()

Dim WkSh_Q As Worksheet
Dim WkSh_Z As Worksheet
Dim rZelle As Range
Dim sFundst As String
Dim Suchbegriff As Boolean
Dim lZeile_Z As Long

Const cstrSPALTE As String = "B" 'Konstante der Suchspalte
Const cstrFUNDSP As String = "C" 'Angabe der Spalte des Fundes
Const cstrZIELSP As String = "D" 'Angabe der Spalte des Ziels

Suchbegriff = False

Set WkSh_Q = ThisWorkbook.Worksheets("Tabelle1")
Set WkSh_Z = ThisWorkbook.Worksheets("Tabelle2")

lZeile_Z = 9 'Startwert - 1 für die Ausgabe der ersten Zeile, hier erfolgt die erste
'erste Ausgabe in Zeile 10

With WkSh_Q.Columns(cstrSPALTE)
Set rZelle = .Find(What:=Suchbegriff, LookAt:=xlPart, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
lZeile_Z = lZeile_Z + 1
WkSh_Z.Cells(lZeile_Z, cstrZIELSP).Value = WkSh_Q.Cells(rZelle.Row, cstrFUNDSP).Value
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address <> sFundst
Else
MsgBox "Der Begriff """ & Suchbegriff & """ wurde nicht gefunden.", _
48, " Hinweis für " & Application.UserName
End If
End With

End Sub

bennyboy
21.03.2009, 08:36
hey jinx ich bin dir sehr dankbar jetzt ist alles klar,

naja kannst dir vielleicht vorstellen ein blinder wie ich, (kann zwar mit excel umgehen einigermaßen) aber hab 0 erfahrung mit vba daher war das ein unlösbarer fall für mich....konnte natürlich nicht wissen das man das dann so explizit dazu schreiben muss....

jetzt passt alles

ich kann jetzt bestimmt bevor ich End_Sub schreibe gleich noch einmal das gleiche makro nur mit anderen such-und kopierzeilen einfügen oder?

jinx
21.03.2009, 08:55
Moin, benny,

das kannst Du machen, für mich wäre das aber ein Fall für eine Prozedur oder Funktion mit Parameterübergabe, der Suchspalte, zu übergebende Spalte, Suchbegriff, Zielspalte und erste Zeile der Ausgabe zu übergeben wären - dies würde die Funktion flexibler halten als ein festgeschriebener Suchbegriff in einem vorherbestimmten Bereich.

Ich habe das Beispiel nicht getestet, es kann also bei der Umsetzung zu einer Fehlermeldung kommen:

Public Sub SuchenKopieren(strZielTabelle As String, strUrsprungstabelle As String, _
strSuchspalte As String, strSuchbegriff As String, _
strDatenspalte As String, strZielspalte As String, _
lZeile_Z As Long)

Dim WkSh_Q As Worksheet
Dim WkSh_Z As Worksheet
Dim rZelle As Range
Dim sFundst As String

Set WkSh_Q = ThisWorkbook.Worksheets(strUrsprungstabelle)
Set WkSh_Z = ThisWorkbook.Worksheets(strZielTabelle)

With WkSh_Q.Columns(strSuchspalte)
Set rZelle = .Find(What:=strSuchbegriff, LookAt:=xlPart, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
WkSh_Z.Cells(lZeile_Z, strZielspalte).Value = WkSh_Q.Cells(rZelle.Row, strDatenspalte).Value
lZeile_Z = lZeile_Z + 1
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address <> sFundst
Else
MsgBox "Der Begriff """ & Suchbegriff & """ wurde nicht gefunden.", _
48, " Hinweis für " & Application.UserName
End If
End With

End Sub
<hr>
Sub Aufruf_SuchenKopieren()

SuchenKopieren "Zieltabelle", "Ursprungstabelle", "B", False, "C", "D", 10
SuchenKopieren "Zieltabelle", "Ursprungstabelle", "B", "Suchbegriff", "D", "D", 25

End Sub

bennyboy
21.03.2009, 09:04
damit kann ich wieder recht wenig anfangen, sorry


du musst denken ich hab damit noch nie zutun gehabt wie soll ich das jetzt umsetzen??

du meintest doch ich kann es einfach unten noch einmal reinkopieren oder?

wenn ja muss da noch irgendne anweisung rein, denn er bringt mir ne fehlermeldung wenn ich das gleiche makro (das ja jetzt funktioniert) noch einmal reinkopiere nur mit anderen such-und zielspalten!

was ist mit End Sub End With und und und....kein plan :-)

jinx
21.03.2009, 09:13
Moin, benny,

ich würde den Code innerhalb einer Prozedur erweitern:

Public Sub SuchenKopieren()

Dim WkSh_Q As Worksheet
Dim WkSh_Z As Worksheet
Dim rZelle As Range
Dim sFundst As String
Dim Suchbegriff As Boolean
Dim lZeile_Z As Long

Set WkSh_Q = ThisWorkbook.Worksheets("Tabelle1")
Set WkSh_Z = ThisWorkbook.Worksheets("Tabelle2")

'==
'Anweisung 1
Suchbegriff = False
lZeile_Z = 9 'Startwert - 1 für die Ausgabe der ersten Zeile, hier erfolgt die erste
'erste Ausgabe in Zeile 10

With WkSh_Q.Columns("B")
Set rZelle = .Find(What:=Suchbegriff, LookAt:=xlPart, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
lZeile_Z = lZeile_Z + 1
WkSh_Z.Cells(lZeile_Z, "D").Value = WkSh_Q.Cells(rZelle.Row, "C").Value
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address <> sFundst
Else
MsgBox "Der Begriff """ & Suchbegriff & """ wurde nicht gefunden.", _
48, " Hinweis für " & Application.UserName
End If
End With

'==
'Anweisung 2
Suchbegriff = "benny"
lZeile_Z = 24 'Startwert - 1 für die Ausgabe der ersten Zeile, hier erfolgt die erste
'erste Ausgabe in Zeile 10

With WkSh_Q.Columns("C")
Set rZelle = .Find(What:=Suchbegriff, LookAt:=xlPart, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
lZeile_Z = lZeile_Z + 1
WkSh_Z.Cells(lZeile_Z, "D").Value = WkSh_Q.Cells(rZelle.Row, "E").Value
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address <> sFundst
Else
MsgBox "Der Begriff """ & Suchbegriff & """ wurde nicht gefunden.", _
48, " Hinweis für " & Application.UserName
End If
End With

Set rZelle = Nothing
Set WkSh_Z = Nothing
Set WkSh_Q = Nothing

End Sub
Es kann immer nur eine Prozedur eines Namens in einem Modul geben. Wenn Du mit zwei Makros arbeiten möchtest, musst Du das zweite umbenennen, z. B. auf SuchenKopieren_D als Hinweis, dass in dem Makro in der Spalte D gesucht wird.

bennyboy
21.03.2009, 13:03
vielen vielen dank !!!

echt wahnsinn du hast echt was drauf!!


finde es so wie so total nett dass manche leute hier ihre zeit für solche ANFÄNGER opfern bis auch der letzte alles verstanden hat :-)


Benny