Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
960to964
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
960to964
960to964
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Zeile von bestimmtem Wert bestimmen

Zeile von bestimmtem Wert bestimmen
12.03.2008 17:10:37
Peter

Guten Tag
Ich habe eine Liste mit Ordnungsnummern in Tabelle "Auswertung". Jede Ordnungsnummer kommt in der Tabelle "Details" (gleiche Arbeitsmappe) ebenfalls vor. Die Ordnungsnummern stehen in einem Spaltenbereich, der mit "Referenz" benannt ist.
Nun möchte ich in der Tabelle Auswertung hinter jeder Ordnungsnummer mittels Formelabfrage die Zeilennummer, wo der entsprechende Wert in der Tabelle Details steht, eintragen. Weiss jemand, mit welcher Funktion so etwas zu bewerkstelligen ist?
Beispiel
TABELLE AUSWERTUNG
Spalte A
Zeile 1: 1093
Zeile 2: 1313URD
TABELLE DETAILS
Spaltenbereich mit Name "Referenz"
...
Zeile 14: 1313URD
...
...
Zeile 17: 1093
Kann ich aufgrund der Werte 1093 resp. 1313URD die Zeilen 14 resp. 17 aus der Tabelle DETAILS bestimmen?
Danke für eine Rückmeldung.
Peter

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 17:59:20
Peter Feustel
Hallo Peter,
wenn es auch ein Makro sein darf, dann so:


Public Sub ZeileSuchen()
Dim Wksh_Q  As Worksheet
Dim WkSh_Z  As Worksheet
Dim lZeile  As Long
Dim iSpalte  As Variant
Dim rZelle  As Range
Set Wksh_Q = Worksheets("Details")
Set WkSh_Z = Worksheets("Auswertung")
For Each iSpalte In ActiveWorkbook.Names
With ActiveWorkbook.Worksheets("Details")
If iSpalte.RefersToRange.Address = .Range(.Cells(1, iSpalte.RefersToRange.Column), _
.Cells(65536, iSpalte.RefersToRange.Column)).Address Then
iSpalte = iSpalte.RefersToRange.Column
Exit For
End If
End With
Next iSpalte
With Wksh_Q.Columns(iSpalte)
For lZeile = 1 To WkSh_Z.Cells(Rows.Count, 1).End(xlUp).Row
Set rZelle = .Find(WkSh_Z.Cells(lZeile, 1).Value, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
WkSh_Z.Cells(lZeile, 2).Value = rZelle.Row
End If
Next lZeile
End With
End Sub


Gruß Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 20:08:50
Peter
Hallo Peter
VBA ist durchaus willkommen.
Bei der Zeile
With Wksh_Q.Columns(iSpalte)
ist Endstation. Anwendungs- oder objektdefinierter Fehler
Wolltest du da allenfalls auf meinen benannten Bereich "Referenz" verweisen?
Da du mir schon eine VBA Lösung bietest, könnte man vielleicht mit dem vorhandenen Code "eine Brücke" zum letztlichen Ziel machen.
Wenn ich die Zeilennummer habe, kann ich aufgrund des Ereignismakros (vgl. nachfolgend) dann mittels Doppelklick auf die Zeilennummer in der Tabelle "Details" auf der entsprechenden Zeile landen.
Mit anderen Worten: wenn ich die Zeilennummer des Eintrags "1313URD" habe und auf die Zeilennummer doppelklicke, komme ich mit Doppelklick genau auf die Zeile in der Tabelle Details, wo dieser Eintrag steht.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, ActiveSheet.Range("A1:A1500")) Is Nothing Then Exit Sub   'ActiveSheet  _
ist
_
'Tabelle Auswertung
If Int(Val(Target.Value)) = Target.Value _
And Target.Value <= ActiveSheet.Rows.Count _
And Target.Value > 0 Then
ThisWorkbook.Sheets("Details").Select
ActiveSheet.Range("A" & Target.Value).Select
End If
End Sub


Vielen Dank für deine Hilfe, sei es zur ersten oder auch zur erweiterten Frage.
Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 20:27:51
Peter Feustel
Hallo Peter,
hier findest du meine Test-Mappe, wahrscheinlich enthält bei dir iSpalte nicht den gewünschten Wert.
Evtl. habe ich deine Frage bezüglich der Spalte mit dem Namen "Referenz" falsch verstanden.
Schau einmal und berichte ggf. https://www.herber.de/bbs/user/50695.xls
Gruß Peter

AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 21:27:22
Peter
Hallo Peter
Vielen Dank für die Testmappe.
Ich habe nun verstanden, weshalb es bei mir nicht funktioniert hat.
"Referenz" ist bei mir ein benannter Bereich in einer x-beliebigen Spalte der Tabelle "Details", jedoch nur ein Teil davon (z.B. D2:D1200).
Wenn ich richtig verstehe, läuft dein Code solange, als eine ganze Spalte ein benannter Bereich ist und nicht vorher schon eine andere Spalte mit einem Namen benannt wurde. Nach Möglichkeit möchte ich die Spalte aufgrund des benannten Bereiches bestimmen - in einer Excel Formel würde das wie folgt gehen:
=SPALTE(Referenz) - mit dieser Abfrage, in VBA übersetzt, könnte die Bestimmung der relevanten Spalte erfolgen. Ich habe probiert mit
iSpalte = Column(Referenz), doch das funktioniert nicht.
Wenn ich probehalber der Variablen die entsprechende Spaltennummer übergeben (z.B. iSpalte = 4), dann funktioniert der Code.
Somit bleibt die Frage, wie ich =spalte(Referenz) in VBA umsetze damit ich die Spaltennummer dann der Variabel iSpalte zuweisen kann.
Gruss, Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 22:07:41
Peter Feustel
Hallo Peter,
ich hatte es vermutet, dass wir irgendwie aneinander vorbeigedacht haben.
Versuch es einmal so:


Public Sub ZeileSuchen()
Dim Wksh_Q   As Worksheet
Dim WkSh_Z   As Worksheet
Dim lZeile   As Long
Dim Bereich  As Variant
Dim rZelle   As Range
Set Wksh_Q = Worksheets("Details")
Set WkSh_Z = Worksheets("Auswertung")
Bereich = Names("Referenz").RefersToRange.Address(0, 0)
With Wksh_Q.Range(Bereich)
For lZeile = 1 To WkSh_Z.Cells(Rows.Count, 1).End(xlUp).Row
Set rZelle = .Find(WkSh_Z.Cells(lZeile, 1).Value, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
WkSh_Z.Cells(lZeile, 2).Value = rZelle.Row
End If
Next lZeile
End With
End Sub


Gruß Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 22:16:51
Peter
Hallo Peter
Vielen Dank, das funktioniert auch so.
Die Fragen des letzten Threads bleiben (hat sich mit deiner Mitteilung überschnitten).
Freundlicher Gruss, Peter

AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 22:11:02
Peter
Hallo Peter
In der Zwischenzeit habe ich die Lösung gefunden:


Public Sub ZeileSuchen()
Dim Wksh_Q  As Worksheet
Dim WkSh_Z  As Worksheet
Dim lZeile  As Long
Dim iSpalte  As Variant
Dim rZelle  As Range
Set Wksh_Q = Worksheets("Details")
Set WkSh_Z = Worksheets("Auswertung")
iSpalte = Range("Referenz").Column
With Wksh_Q.Columns(iSpalte)
For lZeile = 1 To WkSh_Z.Cells(Rows.Count, 1).End(xlUp).Row
Set rZelle = .Find(WkSh_Z.Cells(lZeile, 1).Value, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
WkSh_Z.Cells(lZeile, 2).Value = rZelle.Row
End If
Next lZeile
End With
End Sub


Ich habe noch zwei Fragen:
1) Weshalb fängt dieser Code mit "Public Sub" und nicht nur mit "Sub" an - was ist der Unterschied hier?
2) Was muss ich im code ändern, damit ich bei einer Zeile, wo kein Eintrag ist, nicht die Zeilennummer der ersten leeren Zeile, sondern nichts erhalte (gelb markierte Felder)?
Vielen Dank für deine Hilfe!
Peter
https://www.herber.de/bbs/user/50697.xls

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
12.03.2008 22:34:43
Peter Feustel
Hallo Peter,
hier noch einmal meine letzte Variante mit Dokumentation.
Zu deinen Fragen: Public Sub und Sub sind identisch. Da VBA aber wenn Public nicht geschrieben wird, dieses automatisch annimmt (als Fehlwert, ebenso, wie ein nicht geschriebenen Value bei z. B. Range("A1").Value) schreibe ich es immer mit.
Bei mir wird bei nicht gefundenen Werten auch nichts eingetragen, das regelt sich eigentlich durch den If Not rZelle Is Nothing Then - denn nur dann wird eine Zeilen-Nummer eingetragen.


Public Sub ZeileSuchen()
Dim Wksh_Q   As Worksheet  ' das Quell-Tabellenblatt - die Herkunft der Daten
Dim WkSh_Z   As Worksheet  ' das  Ziel-Tabellenblatt - das Ausgabe-Tabellenblatt
Dim Bereich  As Variant    ' der Spaltenbereich mit Namen "Referenz"
Dim lZeile   As Long       ' For/Next Zeilen-Index
Dim rZelle   As Range      ' die Zelle mit dem gefundenen Suchbegriff
   Application.ScreenUpdating = False    ' den Bildschirm-Update unterbinden
   Set Wksh_Q = Worksheets("Details")    ' hieraus werden die Zeilen-Nummern gelesen
   Set WkSh_Z = Worksheets("Auswertung") ' hierhin werden die Zeilen-Nummern geschrieben
   On Error Resume Next                  ' falls der Spaltenbereich nicht gefunden wird
   Bereich = Names("Referenz").RefersToRange.Address(0, 0) ' den Spaltenbereich suchen
   If Bereich = "" Then                  ' wurde der Spaltenbereich nicht gefunden ?
      MsgBox "Der Spaltenbereich mit Namen ""Referenz"" wurde nicht gefunden.", _
         48, "   Hinweis für " & Application.UserName
      Exit Sub                           ' die Sub-Routine hier verlassen/beenden
   End If
   With Wksh_Q.Range(Bereich)            ' im Spaltenbereich soll gesucht werden
      For lZeile = 1 To WkSh_Z.Cells(Rows.Count, 1).End(xlUp).Row
         Set rZelle = .Find(WkSh_Z.Cells(lZeile, 1).Value, LookAt:=xlWhole, LookIn:=xlValues)
         If Not rZelle Is Nothing Then                 ' wurde der Wert gefunden ?
            WkSh_Z.Cells(lZeile, 2).Value = rZelle.Row ' die gefundene Zeile übergeben
         End If
      Next lZeile
   End With
   Application.ScreenUpdating = True     ' den Bildschirm-Update wieder zulassen
End Sub 

     Code eingefügt mit Syntaxhighlighter 4.4

Gruß Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
13.03.2008 10:10:27
Peter
https://www.herber.de/bbs/user/50701.xls
Hallo Peter
Vielen Dank für den Code mit ausführlicher Beschreibung.
Ich habe den Code noch leicht ergänzt, so dass bei mir bei Zellen, wo kein Wert drin ist (grün markiert), nichts eingetragen wird, resp. ein allenfalls bestehender Eintrag (y) gelöscht wird.
Wenn in der Zelle zwar ein Wert drin ist (orange markiert), der jedoch in der Tabelle "Details" nicht gefunden wird, wird auch nichts eingetragen, ein allenfalls bestehender Eintrag (x) wird jedoch nicht gelöscht.
Wie stelle ich es an, dass auch das "x" gelöscht wird?
Danke für eine gelegentliche Rückmeldung.
Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
13.03.2008 10:52:30
Peter Feustel
Hallo Peter,
so sollte es funktionieren - und auch deine heutige neue Anfrage wird mit beantwortet


'
'   Ich habe eine Liste mit Ordnungsnummern in Tabelle "Auswertung".
'   Jede Ordnungsnummer kommt in der Tabelle "Details" (gleiche Arbeitsmappe)
'   ebenfalls vor.
'   Die Ordnungsnummern stehen in einem Spaltenbereich, der mit "Referenz" benannt ist.
'                                       ==============           ========
'   Nun möchte ich in der Tabelle Auswertung hinter jeder Ordnungsnummer mittels
'   Formelabfrage die Zeilennummer, wo der entsprechende Wert in der Tabelle Details
'   steht, eintragen.
'
'   Kann ich aufgrund der Werte 1093 resp. 1313URD die Zeilen 14 resp. 17 aus der
'   Tabelle DETAILS bestimmen?
'
'   Anmerkung: Hier im Test ist der Spaltenbereich mit Namen "Referenz" = C10:C40
'
Public Sub ZeileSuchen()
Dim Wksh_Q   As Worksheet  ' das Quell-Tabellenblatt - die Herkunft der Daten
Dim WkSh_Z   As Worksheet  ' das  Ziel-Tabellenblatt - das Ausgabe-Tabellenblatt
Dim Bereich  As Variant    ' der Spaltenbereich mit Namen "Referenz"
Dim lZeile   As Long       ' For/Next Zeilen-Index
Dim rZelle   As Range      ' die Zelle mit dem gefundenen Suchbegriff
   Application.ScreenUpdating = False    ' den Bildschirm-Update unterbinden
   Set Wksh_Q = Worksheets("Details")    ' hieraus werden die Zeilen-Nummern gelesen
   Set WkSh_Z = Worksheets("Auswertung") ' hierhin werden die Zeilen-Nummern geschrieben
   On Error Resume Next                  ' falls der Spaltenbereich nicht gefunden wird
   Bereich = Names("Referenz").RefersToRange.Address(0, 0) ' den Spaltenbereich suchen
   If Bereich = "" Then                  ' wurde der Spaltenbereich nicht gefunden ?
      MsgBox "Der Spaltenbereich mit Namen ""Referenz"" wurde nicht gefunden.", _
         48, "   Hinweis für " & Application.UserName
      Exit Sub                           ' die Sub-Routine hier verlassen/beenden
   End If
   With Wksh_Q.Range(Bereich)            ' im Spaltenbereich soll gesucht werden
      For lZeile = 1 To WkSh_Z.Cells(Rows.Count, 1).End(xlUp).Row
         Set rZelle = .Find(WkSh_Z.Cells(lZeile, 1).Value, LookAt:=xlWhole, LookIn:=xlValues)
         If Not rZelle Is Nothing Then                 ' wurde der Wert gefunden ?
            WkSh_Z.Cells(lZeile, 2).Value = rZelle.Row ' die gefundene Zeile übergeben
          Else
            WkSh_Z.Cells(lZeile, 2).Value = ""         ' die NICHT gefundene Zelle löschen
         End If
      Next lZeile
   End With
   Application.ScreenUpdating = True     ' den Bildschirm-Update wieder zulassen
End Sub
'
'   Angenommen, ich habe ich meinem Workbook einen Bereich, der mit "Referenz" benannt
'   ist und in Worksheet "Details" zu finden ist.
'
'   Wie kann ich anhand des Namens "Referenz" mit VBA abfragen, in welchem Worksheet
'   der Bereich steckt?
'
Sub WelchesBlatt()
Dim sBlattname As String
   On Error Resume Next
   sBlattname = Application.Names("Referenz").RefersTo
   If sBlattname = "" Then
      MsgBox "Der gesuchte Bereich wurde nicht gefunden.", _
         48, "   Hinweis für " & Application.UserName
      Exit Sub
   End If
   sBlattname = Mid(sBlattname, 2, InStr(sBlattname, "!") - 2)
   MsgBox "Der gesuchte Bereich ist im Worksheet:  """ & sBlattname & """.", _
      , "   Information für " & Application.UserName
End Sub 

     Code eingefügt mit Syntaxhighlighter 4.4


Gruß Peter

Anzeige
AW: Zeile von bestimmtem Wert bestimmen
13.03.2008 12:00:38
Peter
Hallo Peter
Vielen Dank, jetzt ist alles perfekt!
Peter

183 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige