Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Adresse von Zelle mit gesuchtem Wert

Betrifft: Adresse von Zelle mit gesuchtem Wert von: Marco
Geschrieben am: 07.09.2014 00:05:48

Hallo zusammen,

ich will etwas ganz einfaches, kriege es aber beim besten Willen nicht hin: ich will Spalte A durchsuchen und die Zeilennummer der Zelle haben, in der der Wert gefunden wurde. Es handelt sich um eine Liste von 1 bis x insofern ist eine eindeutige Suche wichtig (wenn 2 gesucht wird will ich nur die Zelle erwischen wo 2 drinsteht, nicht auch noch 12;20;21 etc.).

Habe es sowohl mit Find() versucht, als auch mit etwas umständlichen Konstrukten mit Offset in einer Do Until Schleife bzw. mit If/Else in einer For-Schleife... Alles erfolglos, irgendeine Fehlermeldung kommt immer.

Wäre super wenn jemand die Lösung hätte, langsam verzweifel ich wirklich (v.a. weil es so eine simple Aufgabe ist)

Viele Grüße

P.s. in diesem Zusammenhang gleich noch was, was mir beim rumprobieren nicht klar geworden ist. In einer der besagten Schleifen die ich ausprobiert habe, bleibt meine Variable leer, obwohl die Bedingung erfüllt ist und ActiveCell.Row auch einen Wert hat. Kann mir jemand sagen warum?

Range("A13").Activate
For i = 13 To Range("J1").Value - 1
 If ActiveCell.Value = TextBox1.Value Then
    Anz = ActiveCell.Row
    GoTo WerteInZellenSchreiben
 Else
    Cells(i, 1).Offset(1, 0).Activate
 End If
Next i

  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: {Boris}
Geschrieben am: 07.09.2014 00:45:22

Hi Marco,

Frage 1 ist ohne Beispiel total kryptisch und somit (für mich) nicht nachvollziehbar.
Und auch Deine P.S.-Frage beinhaltet uns nicht bekannte Zellinhalte (J1), nicht bekannte Sprungmarken (Goto...) sowie nicht explizit deklarierte Variablen.

Und nach Deinem Beispielcode zu urteilen (nix für ungut!) solltest Du Deinen VBA-Level (gut) runterschrauben.

VG, Boris


  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: Mullit
Geschrieben am: 07.09.2014 00:53:09

Hallo,

Find böte sich da schon an:

Option Explicit

Public Sub test()
Dim objRange As Range
Set objRange = Columns(1).Find(What:=Tabelle1.TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole) _

If Not objRange Is Nothing Then
  If Tabelle1.TextBox1.Value <> vbNullString Then
    MsgBox "ZeilenNr.:  " & objRange.Row
  Else
    MsgBox "Die Box ist leer!", vbExclamation
  End If
  Set objRange = Nothing
Else
  MsgBox "Wert nicht vorhanden!", vbExclamation
End If
End Sub

Gruß,


  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: Adis
Geschrieben am: 07.09.2014 04:18:35

Hallo

die einzige Erklaerung die mir einfaellt warum es nicht laeuft ist der Wert in Zelle "J1".
İst dieser Wert unter 13 kann die Schleife nicht richtig laufen. Ich arbeite lieber mit
einer Variable für die Endzahl und prüfe ob der Wert zu klein ist. MsgBox Fehlermeldung
Ansonsten kann man auf Cells().Activate verzichten und lieber über Cells(i,"A") prüfen

Endzahl = Range("J1").Value
If Endzahl < 13 Then MsgBox Endzahl & " EndZahl < 13"

For i = 13 To Endzahl - 1
If Cells(i, "A") = TextBox1.Value Then
Anz = i
GoTo WerteInZellenSchreiben
End If
Next i
MsgBox "No Find"
Exit Sub

Gruss Adis


  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: Marco
Geschrieben am: 07.09.2014 11:04:11

Moin,

erstmal danke für die schnellen und zahlreichen Antworten! Habe jetzt die gesuchte Zeilenummer mit Find rausbekommen.

@Boris:
Du hast natürlich Recht, mich hat dieses Problem so gewurmt dass ich im Eifer des Gefechts ein paar Infos unterschlagen hab... Am Endes des Posts nochmal der vollständige Code, hoffentlich wird daraus ersichtlich warum die Variable leer bleibt.

Zum Thema VBA-Level kann ich nicht viel sagen. Mir fehlt der Vergleich mit anderen, von daher war "Gut" ein Schuss ins Blaue. Da man bei den meisten (Programmier-)Sprachen ja mehr versteht als man selber sagen/schreiben kann, dachte ich es passt schon.

@Mullit:
Danke dir für den Code, der hat zwei meiner Fehler aufgedeckt :) Hatte alles als Integer deklariert, was natürlich nicht funktionieren kann wenn man nach einem Range-Objekt sucht. Außerdem bin ich nicht drauf gekommen, dass man die Reihe einfach über die Variable rauskriegt, ich wollte immer noch ein .Row an die Find Methode dranhängen.

Was mir nur aufgefallen ist beim testen: dein If-Block läuft problemlos durch und mir wird in der MsgBox auch die richtige Zeile ausgegeben. Ich kann die Zeilennummer aber nach der MsgBox nichtmehr weiterbenutzen. Wenn ich bspw. nach dem zweiten End If noch Range("I" & Anz.Row).Value = Me.TextBox2.Value schreibe heißt es "Objektvariable oder With-Blockvariable nicht festgelegt". Schreibe ich es hingegen direkt unter MsgBox "ZeilenNr.: " & Anz.Row dann klappt es. Weißt du woran das liegt?

@Adis:
Interessante Lösung sich die Zeile einfach mit i aus Cells zu holen. Nachvollziehbar, aber ich wäre nie drauf gekommen. Bei deiner Schleife ist es das gleiche Problem was ich vorher auch hatte: obwohl die Bedingung erfüllt ist (bin es mal mit F8 durchgegangen), weißt er Anz keinen Wert zu und läuft weiter durch bis Endzahl-1 erreicht ist. Dann gibts halt die MsgBox "No Find"

-----
Hier nochmal der komplette Code, vielleicht sieht man daraus warum die Variable leer bleibt und die Schleife bis zum Ende durchläuft. In Zelle J1 steht das Ende der Spalte A ermittelt durch =ANZAHL2(A:A), in diesem Fall 19.

Private Sub CommandButton1_Click()

Dim Endzahl, i, Anz As Integer
'Dim Anz As Range

ufPositionSchließenEUR.Hide

'Set Anz = Worksheets("Positionsdetails") _
        .Range("A13:A" & Range("J1").Value).Find(What:=TextBox1.Value)


'meine Schleife aus dem Ausgangspost
Range("A13").Activate
For i = 13 To Range("J1").Value - 1
 If ActiveCell.Value = TextBox1.Value Then
    Anz = ActiveCell.Row
    GoTo WerteInZellenSchreiben
 Else
    Cells(i, 1).Offset(1, 0).Activate
 End If
Next i

'Adis Variante
Endzahl = Range("J1").Value
If Endzahl < 13 Then MsgBox Endzahl & " EndZahl < 13"

For i = 13 To Endzahl - 1
 If Cells(i, "A") = TextBox1.Value Then
    Anz = i
    GoTo WerteInZellenSchreiben
 End If
Next i
MsgBox "No Find"

WerteInZellenSchreiben:
'Werte aus dem Formular in die entsprechenden Zellen schreiben
Range("I" & Anz).Value = Me.TextBox2.Value
Range("U" & Anz).Value = Me.TextBox3.Value

End Sub
Wie gesagt, mit dem auskommentierten Find kriege ich das Problem gelöst. Mir gehts nur noch darum, warum sowohl meine als auch Adins Schleife weiter als ihr Abbruchkriterium laufen und die Variable Anz leer bleibt. Ich habe beide Schleifen einzeln getestet, nur im obigen Code stehen sie zusammen.

Viele Grüße


  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: Adis
Geschrieben am: 07.09.2014 16:51:58

Hallo

mir faellt zur Formel Anzahl2 noch etwas ein. Ist jede Zeile in Spalte A mit einer Zahl belegt oder gibt es -Leere Zellen- dazwischen? Dann stimmt der Wert 19 nicht mehr! Weil nur die vollen Zeilen gezaehlt werden! Die leeren bleiben unberücksichtigt.

Es gibt aber noch eine simple Variante um das Problem völlig zu umgehen: End(xlUp)
For i = 13 To Range("A200").End(xlUp).Row. Hier sucht Excel selbst die letzte Zeile.
Warum wurde die Zahl -1 abgezogen? Dann laueft die Schleife ja nie bis zum Ende!

Gruss Adis


  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: Marco
Geschrieben am: 07.09.2014 17:50:14

Hallo Adis,

es gibt keine leeren Zellen zwischen den Werten in Spalte A. Habe alle eigentlich leeren Zellen mit ' gefüllt, die Formel zählt schon richtig.
Um die ganze Sache mal klarere zu machen habe ich eine Beispieldatei erstellt, die die wesentlichen Sachen enthält. Mein Ziel ist es in dem UserForm eine Positionsnummer einzugeben, und für diese Position ein paar Werte zu berechnen (über das bisher auskommentierte Makro). Dafür brauchte ich halt die Zeilennummer dieser gesuchten Position.

Beispieldatei: https://www.herber.de/bbs/user/92509.xlsm

Warum -1 kann ich dir nicht sagen... Ursprünglich hatte ich den normalen Wert aus J1 aber dann wurden die Werte eine Zeile weiter unten reingeschrieben als geplant. Warum ist mir nicht klar, habs dann mit -1 probiert und es hat funktioniert...

Danke für deinen Tipp mit End(xlUp). Ich hab mit VBA erst seit gut einem Monat wirklich zu tun und will es unter allen Umstände vermeiden, Sachen "falsch" oder unelegant zu lernen. Ist generell ne kleine Macke von mir. Bei dem aktuellen Projekt ist es zwar egal, aber falls später mal was kommen sollte wo bspw. nicht nur die Funktion, sondern auch die "Schönheit" des Codes und die Bearbeitungsgeschwindigkeit wichtig ist, wollte ich für das Ermitteln des Endes eine möglichst optimale Lösung haben. Über meinen Thread hier http://www.office-loesung.de/p/viewtopic.php?f=166&t=666391#p2773539 kam der Vorschlag mit dem Namensmanager zu arbeiten. Da ich die Variable aber nicht mit diesem Namen verbunden bekommen hab, habe ich den Wert erstmal in J1 geschrieben.

Viele Grüße


  

Betrifft: AW: Adresse von Zelle mit gesuchtem Wert von: Adis
Geschrieben am: 07.09.2014 23:13:34

Hallo

mit Workbook Namen arbeiten geht auch. Hat einen Vorteil zum normalen Range über Adresse.
Verschiebt man Zeilen oder Spalten dann wandert die Referenz Adresse automatisch mit.
Bei meinem PC XP wird oben Links in der Bearbeitungsleiste die Zell Adresse angezeigt.
Ich kann die Zelle oben anklicken und dort einen Namen reinschreiben. Enter drücken.
Jetzt wird nicht mehr die Zell Adresse angezeigt, sondern der neue "Name" der Zelle.
Intern weiss Excel um welche Adresse es sich handelt. In Range Name mit "" einsetzen.

For i = 13 To Range("Anz").Value
Jetzt wird direkt die aktuelle Zahl aus der Zelle geladen. Dauert seine Zeit bis man
das alles als Programmierung gelernt und verstanden hat. Nicht aufgeben, lohnt sich ...

Gruss Adis


 

Beiträge aus den Excel-Beispielen zum Thema "Adresse von Zelle mit gesuchtem Wert"