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 RefEdit
BildScreenshot zu RefEdit RefEdit-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Wert finden in einem Range Bereich


Betrifft: Wert finden in einem Range Bereich von: Bianca
Geschrieben am: 12.07.2017 17:42:49

Hallo Zusammen,

wie die Überschrift schon andeutet geht es um ein Makro in Excel.
ich möchte eine Nummer in einer Spalte suchen, Spalte B
Ich habe es mit dem Code bisher geschrieben:

For i = 1 To Sheets(2).UsedRange.Rows.Count

If (Sheets(2).Range("B" & i) = x) Then
mach was......

next i

Die Nummer welche gesucht wird, lese ich in x ein z.B 81. Was so lange funktioniert wie die Zahl alleine in einer Zelle steht. Jetzt kommt es vor dass der Zelleninhalt so aussieht: 80-81-82 Da funtioniert es nunmal nicht.
Gibt es da eine möglichkeit das er dann die Zahl 81 trotzdem findet.

LG Bianca

  

Betrifft: AW: Wert finden in einem Range Bereich von: Hajo_Zi
Geschrieben am: 12.07.2017 17:58:14

mache es mit find ist schneller.
Die Variable X ist nicht belegt in Deinem Code.

GrußformelHomepage

Ich gebe keinen Dank für eine Rückmeldung, da ich durch solche Beiträge nicht meine Beitragszahl erhöhen muss.
Rückmeldung ist ja in der Heutigen Zeit nicht üblich und die wenigen die eine Rückmeldung geben, mögen mir das verzeihen, das kein Danke für eine Rückmeldung kommt.


  

Betrifft: AW: Wert finden in einem Range Bereich von: Bianca
Geschrieben am: 13.07.2017 10:57:07

Danke für die schnelle Antwort.

Es tut mir nur leid das das mir bisher nicht wirklich weiter hilft. Habe jetzt schon ne stunde das internet durchforstet aber wie ich diese funtion einsetzen soll weiß ich jetzt immer noch nicht.

Und was finden genau macht auch nicht. Also findet es da tatsächlich nur werte die 81 sind oder findet es auch werte wie z.B 181 das möchte ich ja nicht.

Public Function KS_Auswertung()
 
Dim x As String, y As Double, GerNr As String
Dim arrPol1(1 To 200, 1 To 3) As Double
Dim arrPol2(1 To 200, 1 To 3) As Double
Dim arrPol3(1 To 200, 1 To 3) As Double
Dim arrPol4(1 To 200, 1 To 3) As Double
Dim countSchuss As Integer
Dim AnzPole As Integer
Dim AktPol As Integer                                            'hier soll eingelesen werden  _
von welchem Pol gerade die Werte gesucht werden
countSchuss = 0


'~~~~~~Abfrage~GerNr~angegeben~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If UF_KS.TextBox1.Text <> "" Then
GerNr = UF_KS.TextBox1.Text
x = GerNr

End If
'~~~~~~Anzahl~~~Pole~~~suchen~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Worksheets(1).Select
    AnzPole = Left(Cells(16, 2), 1)
Worksheets(2).Select
'~~~~~~Werte-suchen-und-in-Matrix-anlegen~~~~~~~~~
For y = 1 To AnzPole                                     'geht die Pole L1bisL4 durch
    countSchuss = countSchuss + 1
    For i = 1 To Sheets(2).UsedRange.Rows.Count
            If i = 1 Then
            countSchuss = 1
            End If                                   'For i2 = 1 To Sheets(2).UsedRange.Rows. _
Count  'zeile2
    
  • If (Sheets(2).Range("B" & i) = x) Then If (Sheets(2).Range("E" & i) = y) Then If y = 1 Then
  • Sheets(2).Range("Q" & i).Select arrPol1(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol1(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol1(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 ElseIf y = 2 Then Sheets(2).Range("Q" & i).Select arrPol2(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol2(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol2(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 ElseIf y = 3 Then Sheets(2).Range("Q" & i).Select arrPol3(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol3(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol3(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 ElseIf y = 4 Then Sheets(2).Range("Q" & i).Select arrPol4(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol4(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol4(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 End If End If End If Next i Next y '~~~~~~Maximalwerte-ermitteln~~~~~~~~~~~~~~~~~~~~~~~~ MaxL1_imax = Application.Max(Application.Index(arrPol1, 0, 1)) MaxL2_imax = Application.Max(Application.Index(arrPol2, 0, 1)) MaxL3_imax = Application.Max(Application.Index(arrPol3, 0, 1)) MaxL4_imax = Application.Max(Application.Index(arrPol4, 0, 1)) MaxL1_tk = Application.Max(Application.Index(arrPol1, 0, 2)) MaxL2_tk = Application.Max(Application.Index(arrPol2, 0, 2)) MaxL3_tk = Application.Max(Application.Index(arrPol3, 0, 2)) MaxL4_tk = Application.Max(Application.Index(arrPol4, 0, 2)) MaxL1_Qges = Application.Max(Application.Index(arrPol1, 0, 3)) MaxL2_Qges = Application.Max(Application.Index(arrPol2, 0, 3)) MaxL3_Qges = Application.Max(Application.Index(arrPol3, 0, 3)) MaxL4_Qges = Application.Max(Application.Index(arrPol4, 0, 3)) If UF_KS.TextBox1.Text <> "" Then UF_KS.TextBox2.Text = "GerNr: " & "PolNr: " & "max.tk: " & "imax: " & "max.Qges: _ " & Chr(10) _ & " " & " (in ms)" & " (in A) " & " (in kA²s)" & Chr(10) _ & Chr(10) _ & GerNr & " " & Cells(3, 5) & " " & MaxL1_tk & " " & MaxL1_imax & " " & MaxL1_Qges & _ Chr(10) _ & GerNr & " " & Cells(4, 5) & " " & MaxL2_tk & " " & MaxL2_imax & " " & MaxL2_Qges & _ Chr(10) _ & GerNr & "" & Cells(5, 5) & " " & MaxL3_tk & " " & MaxL3_imax & " " & MaxL3_Qges & Chr( _ 10) _ & GerNr & " " & Cells(6, 5) & " " & MaxL4_tk & " " & MaxL4_imax & " " & MaxL4_Qges Else MsgBox ("Bitte GeräteNumer eintragen") End If End Function

    VIleicht hilft der ganze Code bei meinem Problem


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Bianca
    Geschrieben am: 13.07.2017 11:50:47

    Danke für die schnelle Antwort.

    Es tut mir nur leid das das mir bisher nicht wirklich weiter hilft. Habe jetzt schon ne stunde das internet durchforstet aber wie ich diese funtion einsetzen soll weiß ich jetzt immer noch nicht.

    Und was finden genau macht auch nicht. Also findet es da tatsächlich nur werte die 81 sind oder findet es auch werte wie z.B 181 das möchte ich ja nicht.

    Public Function KS_Auswertung()
     
    Dim x As String, y As Double, GerNr As String
    Dim arrPol1(1 To 200, 1 To 3) As Double
    Dim arrPol2(1 To 200, 1 To 3) As Double
    Dim arrPol3(1 To 200, 1 To 3) As Double
    Dim arrPol4(1 To 200, 1 To 3) As Double
    Dim countSchuss As Integer
    Dim AnzPole As Integer
    Dim AktPol As Integer                                            'hier soll eingelesen werden  _
    von welchem Pol gerade die Werte gesucht werden
    countSchuss = 0
    
    
    '~~~~~~Abfrage~GerNr~angegeben~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If UF_KS.TextBox1.Text <> "" Then
    GerNr = UF_KS.TextBox1.Text
    x = GerNr
    
    End If
    '~~~~~~Anzahl~~~Pole~~~suchen~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Worksheets(1).Select
        AnzPole = Left(Cells(16, 2), 1)
    Worksheets(2).Select
    '~~~~~~Werte-suchen-und-in-Matrix-anlegen~~~~~~~~~
    For y = 1 To AnzPole                                     'geht die Pole L1bisL4 durch
        countSchuss = countSchuss + 1
        For i = 1 To Sheets(2).UsedRange.Rows.Count
                If i = 1 Then
                countSchuss = 1
                End If                                   'For i2 = 1 To Sheets(2).UsedRange.Rows. _
    Count  'zeile2
        
  • If (Sheets(2).Range("B" & i) = x) Then If (Sheets(2).Range("E" & i) = y) Then If y = 1 Then
  • Sheets(2).Range("Q" & i).Select arrPol1(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol1(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol1(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 ElseIf y = 2 Then Sheets(2).Range("Q" & i).Select arrPol2(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol2(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol2(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 ElseIf y = 3 Then Sheets(2).Range("Q" & i).Select arrPol3(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol3(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol3(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 ElseIf y = 4 Then Sheets(2).Range("Q" & i).Select arrPol4(countSchuss, 1) = Cells(i, "Q") Selection.Interior.ColorIndex = 39 Sheets(2).Range("U" & i).Select arrPol4(countSchuss, 2) = Cells(i, "U") Selection.Interior.ColorIndex = 39 Sheets(2).Range("Z" & i).Select arrPol4(countSchuss, 3) = Cells(i, "Z") Selection.Interior.ColorIndex = 39 countSchuss = countSchuss + 1 End If End If End If Next i Next y '~~~~~~Maximalwerte-ermitteln~~~~~~~~~~~~~~~~~~~~~~~~ MaxL1_imax = Application.Max(Application.Index(arrPol1, 0, 1)) MaxL2_imax = Application.Max(Application.Index(arrPol2, 0, 1)) MaxL3_imax = Application.Max(Application.Index(arrPol3, 0, 1)) MaxL4_imax = Application.Max(Application.Index(arrPol4, 0, 1)) MaxL1_tk = Application.Max(Application.Index(arrPol1, 0, 2)) MaxL2_tk = Application.Max(Application.Index(arrPol2, 0, 2)) MaxL3_tk = Application.Max(Application.Index(arrPol3, 0, 2)) MaxL4_tk = Application.Max(Application.Index(arrPol4, 0, 2)) MaxL1_Qges = Application.Max(Application.Index(arrPol1, 0, 3)) MaxL2_Qges = Application.Max(Application.Index(arrPol2, 0, 3)) MaxL3_Qges = Application.Max(Application.Index(arrPol3, 0, 3)) MaxL4_Qges = Application.Max(Application.Index(arrPol4, 0, 3)) If UF_KS.TextBox1.Text <> "" Then UF_KS.TextBox2.Text = "GerNr: " & "PolNr: " & "max.tk: " & "imax: " & "max.Qges: _ " & Chr(10) _ & " " & " (in ms)" & " (in A) " & " (in kA²s)" & Chr(10) _ & Chr(10) _ & GerNr & " " & Cells(3, 5) & " " & MaxL1_tk & " " & MaxL1_imax & " " & MaxL1_Qges & _ Chr(10) _ & GerNr & " " & Cells(4, 5) & " " & MaxL2_tk & " " & MaxL2_imax & " " & MaxL2_Qges & _ Chr(10) _ & GerNr & "" & Cells(5, 5) & " " & MaxL3_tk & " " & MaxL3_imax & " " & MaxL3_Qges & Chr( _ 10) _ & GerNr & " " & Cells(6, 5) & " " & MaxL4_tk & " " & MaxL4_imax & " " & MaxL4_Qges Else MsgBox ("Bitte GeräteNumer eintragen") End If End Function

    VIleicht hilft der ganze Code bei meinem Problem


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Piet
    Geschrieben am: 13.07.2017 15:53:35

    hLLO bİANCA,

    Im Augenblick sehe ich keinen logischen Grund warum der Code nicht laufen soll? Vielleicht übersehe ich etwas?

    Man kaönnte ihn wahrscheinlich verkürzen, habe ihn mal umgeschrieben. Auf "x" kann man verzichten wenn man direkt die GerNr angibt! Sheets + Range Select ist auch nicht notwendig, wir machen das ohne. Den geanderten Code bitte zuerst in einer Test-Datei testen ob er richtig laeuft.

    mfg Piet


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Bianca
    Geschrieben am: 13.07.2017 16:41:15

    Hallo Piet,

    Naja eigentlich ging es ja nicht darum ob der Code funtioniert.

    Sondern um folgende Stelle :

    If (Sheets(2).Range("B" & i) = x) Then


    HIer sollen die Einträge welche in Spalte B stehen Zelle für Zelle verglichen werden ob diese mit dem Wert in X übereinstimmen. Es geht da um Gerätenummern da aber in einer Zelle auch drei Nummern stehen können z.B 81-82-88 oder 88/66/22 brauch ich ne möglichkeit welche in der Zelle trotzdem die Gesuchte NUmmmer X=88 findet.

    PS: Hab mich ja erst gestern angemeldet hier, vieleicht weiß ich noch nicht wie das geht aber ich seh von dir keinen Abgeänderten Code.


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Bianca
    Geschrieben am: 14.07.2017 12:33:35

    GUten Tag,

    Ich habe bisher alle find() funtionen mal versucht anzuwenden. Aber das geht nicht.
    Der find befehl findet ja alle varianten der gesuchten Zahl, z.B:
    Find 11 dann findet er die auch bei 111 oder 22-11-55 oder 22-111-9
    und ich möchte ja dass er die 11 nur findet wenn sie so in einer Zelle ist:
    11 oder 22-11-88 oder 55/66/11

    Gibt es da eine Möglichkeit???


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Daniel
    Geschrieben am: 14.07.2017 13:05:02

    Hi

    wenn du immer das gleiche Trennzeichen zwischen den Zahlen hast (z.B. "-") dann müsste die Prüfung so aussehen:

    If Instr("-" & Sheets(2).Cells(i, 2) & "-", "-" & x & "-" > 0 Then
    bei verschieden Trennzeichen wird's komplizierter. wenn "-" oder "/" vorkommen können, dann vielleicht so:
    If "-" & Sheets(2).Cells(i, 2) & "-" Like "*[-/]" & x & "[-/]*" Then

    zur Erläuterung:
    die Trennzeichen werden am Anfang und Ende hinzugefügt, damit diese Positionen keine Sonderfälle bilden und ein Wert der dort steht genauso gefunden wird wie wenn er in der Mitte zwischen zwei Trennzeichen stehen würde.

    im Like-Vergleich ist der * ein Joker für beliebige Zeichen.
    die eckigen Klammern deuten an, dass an dieser Stelle ein Zeichen stehen darf und es muss eines der Zeichen sein, die innerhalb der Klammern stehen (dh dort müsstest du alle möglichen Trennzeichen auflisten)

    Gruß Daniel


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Bianca
    Geschrieben am: 14.07.2017 17:49:36

    Hallo Daniel,

    Danke für deine Antwort, bisher konnte ich diese Möglichleit nur antesten aber bisher scheint das die Lösung zu sein.
    Ich schreib ja noch nicht so lange Makros, aber ich habe schon festgestellt das sowas so weit wie möglich idioten sicher sein sollte. ^^ Also das der Enduser so wenig wie möglich beachten muss.
    Ich dachte nähmlich auch schon an einen Split Befehl aber da müsste der User auch noch eingeben welches Trennzeichen er benutzt hat.
    Oder gibt es da auch die möglichkeit wie in deinem Beispiel zu sagen splite entweder mit dem Zeichen "-"oder mit dem "/"????????

    Werde auf jeden Fall noch deineVariante einpflegen und testen, hört sich gut an und den instr() Befehl hab ich noch nicht verwendet so lernich den auch kennen.^^

    Gruß Bianca


      

    Betrifft: AW: Wert finden in einem Range Bereich von: Daniel
    Geschrieben am: 14.07.2017 19:02:26

    Hi

    du kannst ja den Text vorher in eine Variable lesen und dort dann alle möglichen Trennzeichen durch dein Standardtrennzeichen austauschen.
    Dann bist du für die nachfolgende Programmierung einheitlich unterwegs, was die Sache einfacher macht.

    dim txt as string
    
    txt = Sheets(2).Cells(i, 2).Value
    txt = Replace(txt, "/", "-")
    txt = Replace(txt, ";", "-")
    txt = Replace(txt, "\", "-")
    ... ggf wiederholen, bis du alle möglichen Trennzeichen durch hast
    txt = "-" & txt & "-"
    
    If Instr(txt, "-" & x & "-" > 0 Then

    Gruß Daniel


    Beiträge aus den Excel-Beispielen zum Thema "Wert finden in einem Range Bereich"