Find-Methode mit Datum

  • Excel

Es gibt 14 Antworten in diesem Thema. Der letzte Beitrag () ist von peterfido.

    Find-Methode mit Datum

    Hallo Zusammen,

    ich habe ein Problem mit der Find-Methode:
    in der Zelle( 20,14) hab ich ein Datum und eine Uhrzeit zusammen addiert. Da steht dann z.b. 31-10-14 15:30 (Zelle habe ich so formaritert)
    Nun möchte ich diesen Wert in der Zeile 8 suchen (auch dort wurden Datum und Uhrzeit addiert, beide haben das gleiche Format).
    Nachdem der Wert in der Spalte gefunden wurde brauche ich die Nummer der Spalte und muss diese dann einer Variable zuweisen.

    ich habe das ganze zuvor mit einer for 1 to ... Schleife. gemacht aber die Lauftzeit war dann irgenwann zu lange. Desshalb dachte ich es wäre schneller mit der Find-Methode zu arbeiten. Leider klappt das mit der Find-Methode mit dem Datum- Wert nicht...mit Wörtern oder Zahlen funktioniert das Programmm.


    Kann mir jemand weiterhelfen? Ich bin auch nicht aug die Find-Methode angewiesen aber mit For-Schleifen dauerte es einfach zu lange...

    Bisheriger Code oder besser noch eine Datei hier anhängen, wo man das testen kann. Mir fehlt immer die Motivation selbst erst sowas "stricken" zu müssen :)
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    ​Dim bereich As Range
    Set bereich = Sheets("Planung").Rows(8).Find(Cells(18, 14).Value, LookIn:=xlFormulas)
    If bereich Is Nothing Then
    MsgBox "Datum nicht gefunden"
    Else
    MsgBox "Spaltenummer" & bereich.Column & " !"
    End If


    so hab ich es jetzt mal probiert.
    Anbei auch noch die Datei.
    Dateien

    • Planung.zip

      (495,16 kB, 219 mal heruntergeladen, zuletzt: )

    Das folgende funzt bei mir:

    Quellcode

    1. Private Sub los()
    2. Dim sDate As Date
    3. Dim zDate As Date
    4. Dim sZeile As Long
    5. Dim sSpalte As Long
    6. If IsDate(Cells(18, 14).Value) Then
    7. sDate = CDate(Cells(18, 14).Value)
    8. sZeile = 8
    9. With Sheets("Planung")
    10. For sSpalte = 1 To .UsedRange.SpecialCells(xlCellTypeLastCell).Column
    11. If IsDate(.Cells(sZeile, sSpalte).Value) Then
    12. zDate = CDate(.Cells(sZeile, sSpalte).Value)
    13. If sDate = zDate Then
    14. MsgBox "Spaltenummer: " & sSpalte & "!"
    15. Exit Sub
    16. End If
    17. End If
    18. Next
    19. End With
    20. MsgBox "Datum nicht gefunden"
    21. End If
    22. End Sub


    Edit:
    Code noch etwas "optimiert"
    Oben suchst Du mit Äpfel nach Birnen, bzw. Value in xlFormula. Ein cells().formula brachte aber auch kein Ergebnis. So lange dauert das Suchen bei mir nicht. Und wenn doch, ist doch egal auf der Arbeit. Geht in jedem Fall noch schneller als zu Fuss suchen ;)

    Es ging auch ohne das zweite cdate und ohne die Prüfung davor, ob der Wert ein Datum ist. Aber so finde ich es etwas sauberer. Das Weglassen würde die Abarbeitung des Codes etwas beschleunigen.
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „peterfido“ ()

    peterfido schrieb:

    Oben suchst Du mit Äpfel nach Birnen, bzw. Value in xlFormula
    Klingt komisch, aber bei Datum funktioniert's, da bei xlFormulas wohl der echte numerische Datumswert ermittelt wird, während bei xlValues die Zelle nur gefunden wird, wenn der Suchstring im Systemformat angegeben wird(#mm/dd/yyyy#)

    Ich hab's auch nie verstanden, aber irgendwann habe ich mir gemerkt, dass man bei Datum in xlFormulas suchen muss.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Bei mir hatte er da nichts gefunden. (Excel 2007), deswegen habe ich mal weiterprobiert, bis ich es hatte. Möglich, dass da noch mehr Faktoren eine Rolle spielen.
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    Danke der Code funktioniert einwandfrei!
    ich hab jetzt etwas weitergebastelt, aber bin immer noch bei einer Laufzeit von 40 Sekunden +....

    Gibt es eine Möglichkeit den Programm-Ablauf zu beschleunigen?

    Visual Basic-Quellcode

    1. Dim sDate As Date
    2. Dim zDate As Date
    3. Dim sZeile As Long
    4. Dim sSpalte As Long
    5. Dim z As Integer
    6. Dim addiere As Integer
    10. For k = 18 To 30
    11. z = 0
    12. addiere = Cells(k, 18).Value '= Anzahl der Stunden(Stunden *2) weil in die Zeitrechnung mit halben Stunden rechnet
    13. sSpalte = 0
    14. If IsDate(Cells(k, 14).Value) Then
    15. sDate = CDate(Cells(k, 14).Value)
    16. sZeile = 8
    17. With Sheets("Planung")
    18. For sSpalte = 1 To .UsedRange.SpecialCells(xlCellTypeLastCell).Column
    19. If IsDate(.Cells(sZeile, sSpalte).Value) Then
    20. zDate = CDate(.Cells(sZeile, sSpalte).Value)
    21. If sDate = zDate Then
    25. If Cells(k, 27).Value = "X" Then 'in welcher Schich soll gearbeitet werden
    27. For j = sSpalte To 10000
    28. If Cells(9, j) = "1" Then 'Suche nach dem nächsten Möglichen Starttermin in Schicht 1
    30. z = z + 1 'Anzahl der Schleifendurchläufe
    31. Cells(k, 15).Value = Cells(5, j).Value ' Schreibe die Startuhrzeit in Celle (k,15)
    32. If z > addiere + 1 Then GoTo ort2 ' Steige aus wenn die Anzahl der anzahl der schleifen > Stunden +1
    34. If Cells(k, 32) = "1" Then 'Auf welcher Maschine wird gearbeitet
    36. Cells(15, j).Select
    37. Cells(15, j).Value = Cells(k, 1).Value ' Schreibe Positionsnummer in markiete Zelle brauche ich für ein Diagramm
    38. With Selection.Interior 'Färbe die Zelle farbig
    39. .ColorIndex = 5
    40. End With
    41. Cells(k, 15).Value = Cells(7, j).Value ' Schreibe das EndDatum in die Celle (k,15)
    42. Cells(k, 16).Value = Cells(7, j).Value ' Schreibe die EndUhrzeit in die Celle(k,16)
    45. ' Kommentar in die Zelle einfügen
    46. strCom = Cells(8, j).Value & Cells(k, 4).Value
    47. With Cells(15, j)
    48. .ClearComments
    49. .AddComment strCom
    50. End With
    51. End If
    52. End If
    53. Next j
    54. End If
    55. End If
    56. End If
    57. Next
    58. End With
    59. 'MsgBox "Datum nicht gefunden"
    60. End If
    64. ort2:
    66. Next k
    70. Application.ScreenUpdating = True
    72. End Sub


    Die Problematik bei mir ist, dass sehr viele Kombinationsmöglichkeiteng gibt: z.B Schicht 1 mit Maschine 1-3, das gleiche mit Schicht zwei und drei und natürlich muss ich die Schichten zb 1 und 2 oder 2 und 3 oder 1 und 3 oder 1 und 2 und 3 auch kombinieren können.

    Ich bin nicht auf der Suche nach einem kompletten Programm mir wäre schon geholfen, wenn der gepostet Code keine Ewigkeit brauchen würde.
    Dateien

    • Planung.zip

      (563,77 kB, 216 mal heruntergeladen, zuletzt: )

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „saibot231“ ()

    Die 10000 evtl. auf den tatsächlich genutzten Bereich begrenzen. Dann ein Goto um zu einem Next zu springen. Also Nein, das geht auch besser. Mit

    Quellcode

    1. exit for
    brichst Du eine Schleife vorzeitig ab... Du kannst auch die automatische Berechnung während das Makro läuft abschalten. Hinterher wieder aktivieren nicht vergessen.

    Fehler abfangen kann auch nicht schaden.

    Quellcode

    1. On error goto Fehler


    Unten am Ende des Makros dann

    Quellcode

    1. Fehler:
    2. if err.number<>0 then
    3. ...Fehlerbehandlung
    4. err.clear
    5. end if
    7. application.screenupdating=true
    8. application.calculation = xlCalculationAutomatic
    10. end sub


    Edit:
    wenn bestimmte Bereiche immer wieder durchlaufen werden, dann ist die Ausführung aus dem RAM irgendwann schneller. Dazu z.B. alle Werte der Spalte 9 in Variablen abspeichern und diese dann abfragen. Wobei da dann auch wieder einiges zu beachten ist...
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „peterfido“ ()

    Danke schon mal!

    Die 10000 evtl. auf den tatsächlich genutzten Bereich begrenzen.

    ich ermittle unter anderm mit dem Makro den Termin der Fertigstellung, dh. ich weiss nicht wo mein Bereich endet. Hast du da eine besser Idee? Eigentlich láuft diese Schleife ja sowieso nur bis alle Stunden markiert sind und dann steige ich aus (so dachte ich funktioniert die schleife)?




    ... Du kannst auch die automatische Berechnung während das Makro läuft abschalten. Hinterher wieder aktivieren nicht vergessen.
    das mit der autmatischen Berechnung hilft mir unglaublich, das kannte ich bisher noch nicht!


    Fehler abfangen kann auch nicht schaden.

    Quellcode

    1. On error goto Fehler

    Da musst du mir erklären was Fehler abfangen heisst und ich das an welcher Stelle (ON error goto Fehler) einsetzen soll, damit es richtig positioniert ist


    Edit:
    wenn bestimmte Bereiche immer wieder durchlaufen werden, dann ist die Ausführung aus dem RAM irgendwann schneller. Dazu z.B. alle Werte der Spalte 9 in Variablen abspeichern und diese dann abfragen. Wobei da dann auch wieder einiges zu beachten ist...
    [/quote]
    hört sich interessant an, muss mich dazu aber erst noch einlesen!

    Schleifen lasse ich grundsätzlich nur bis zur letzten genutzten Zeile / Spalte (je nachdem) durchlaufen. Wenn er nichts findet, ackert er sonst die 10.000 Stück durch. (Siehe in meinem Code die Usedrange.coloumn). Die Schleife kann man natürlich auch erst später beginnen, wenn man weiß, dass die Daten eh erst ab Spalte 10 beginnen. Das würde bei 1.000 Zeilen wieder 10.000 vergleiche sparen.

    Wenn Fehler auftreten können, z.B. weil zwei ungleiche Werttypen addiert werden sollen oder so, dann kann man vorher sowas mit

    Quellcode

    1. on error goto ...
    abfangen. Der Vorteil ist, dass das Makro nicht mit Pauken und Trompeten mit einer hässlichen Fehlermeldung abschmiert und darüber hinaus die automatische Berechnung und Fensteraktualisierung nicht mehr zurückgestellt wird.

    Man kann auch

    Quellcode

    1. on error resume next
    nutzen, damit Fehler ignoriert werden. Da bekommt man aber evtl. Fehler nicht mit und die Ergebnisse passen unter Umständen nicht. Das nutze ich öfter bei Benutzereingaben wie z.B. Pfade oder so. Direkt nach der Eingabe prüfe ich, ob da ein Fehler aufgetreten ist, z.B. um das Makro abzubrechen

    Quellcode

    1. if err.number <>0 then err.clear: exit sub


    Mit dem RAM arbeiten ist relativ einfach. Einmal ein Array erstellen und dann alle Werte einmal darin ablegen. Danach nur noch mit dem Array arbeiten. Geht bei vielen Durchläufen viel schneller, als jedesmal die Zellen auszulesen.
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    eine idee?

    Hallo ihr Beiden,

    ich hab jetzt ein bisschen rumprobiert und mitlerweile funktioniert das Programm so wie ich es will.
    Jedoch habe ich noch folgendes Problem:
    wenn ich den Makro starte läuft dieser einwandfrei durch, jedoch muss ich >1 mal auf den makro clicken, dass er auch wirklich alles kalkuliert und in die Zellen einträgt. An was kann das liegen?
    Wenn ihr das ausprobieren wollt, einfach mal die Dauer der Stunden in der Excell-Datei verändern und den Makro starten.
    (ich weiss das ich das goto noch drin habe, aber ich weiss nicht wie ich das sonst anders löse.....)

    Visual Basic-Quellcode

    1. Private Sub CommandButton1_Click()
    3. Application.ScreenUpdating = False
    4. Application.Calculation = xlCalculationManual 'automat.Berechnung ausschalten
    6. Dim sDate As Date
    7. Dim zDate As Date
    8. Dim sZeile As Long
    9. Dim sSpalte As Long
    10. Dim z As Integer
    11. Dim addiere As Integer
    13. Dim t As Double
    15. t = Timer
    17. On Error GoTo Fehler
    19. letztezeile = ActiveSheet.Cells(1048576, 1).End(xlUp).Row 'letzte Zeile in Spalte 1 finden
    21. 'Sortieren der Reihenfolge
    22. Rows("18:" & letztezeile).Select
    23. ActiveWorkbook.Worksheets("Planung").Sort.SortFields.Clear
    24. ActiveWorkbook.Worksheets("Planung").Sort.SortFields.Add Key:=Range("A18:A40" _
    25. ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    26. With ActiveWorkbook.Worksheets("Planung").Sort
    27. .SetRange Range("18:" & letztezeile)
    28. .Header = xlGuess
    29. .MatchCase = False
    30. .Orientation = xlTopToBottom
    31. .SortMethod = xlPinYin
    32. .Apply
    33. End With
    38. For k = 18 To letztezeile
    40. If Cells(k, 1).Interior.ColorIndex = xlNone Then
    42. z = 0
    43. addiere = Cells(k, 18).Value '= Anzahl der Stunden(Stunden *2) weil in die Zeitrechnung mit halben Stunden rechnet
    44. sSpalte = 0
    45. If IsDate(Cells(k, 14).Value) Then
    46. sDate = CDate(Cells(k, 14).Value)
    47. sZeile = 8
    48. With Sheets("Planung")
    49. For sSpalte = 1 To .UsedRange.SpecialCells(xlCellTypeLastCell).Column
    50. If IsDate(.Cells(sZeile, sSpalte).Value) Then
    51. zDate = CDate(.Cells(sZeile, sSpalte).Value)
    52. If sDate = zDate Then
    56. Range(Cells(15, sSpalte), Cells(17, 16100)).Select ' Bereich Kommentarfrei und farbfrei machen
    57. Selection.ClearContents
    58. With Selection
    59. .ClearComments
    60. .Interior.Color = xlNone
    61. End With
    63. ' 1 und2 und 3 Schicht
    64. If Cells(k, 28).Value = "x" And Cells(k, 29).Value = "x" And Cells(k, 27).Value = "x" Then 'in welcher Schich soll gearbeitet werden
    66. For j = sSpalte To sSpalte + 300
    67. If Cells(11, j) = "5" Then 'Suche nach dem nächsten Möglichen Starttermin in Schicht 1
    69. z = z + 1 'Anzahl der Schleifendurchläufe
    70. Cells(k, 15).Value = Cells(5, j).Value ' Schreibe die Startuhrzeit in Celle (k,15)
    71. If z = addiere + 1 Then
    72. Cells(k, 15).Value = Cells(5, j).Value
    73. Cells(k, 16).Value = Cells(7, j).Value
    75. If Cells(k + 1, 2) = "x" Then
    76. Cells(k + 1, 11) = Cells(k, 15)
    77. Cells(k + 1, 12) = Cells(k, 16).Value + TimeSerial(0, 30, 0)
    78. End If
    80. GoTo ort2
    81. End If
    83. If Cells(k, 32) = "1" Then 'Auf welcher Maschine wird gearbeitet
    85. Cells(15, j).Select
    86. Cells(15, j).Value = Cells(k, 1).Value ' Schreibe Positionsnummer in markiete Zelle brauche ich für ein Diagramm
    87. With Selection.Interior 'Färbe die Zelle farbig
    88. .ColorIndex = 5
    89. End With
    90. Cells(k, 15).Value = Cells(7, j).Value ' Schreibe das EndDatum in die Celle (k,15)
    91. Cells(k, 16).Value = Cells(7, j).Value ' Schreibe die EndUhrzeit in die Celle(k,16)
    94. ' Kommentar in die Zelle einfügen
    95. strCom = Cells(8, j).Value & Cells(k, 4).Value
    96. With Cells(15, j)
    97. .ClearComments
    98. .AddComment strCom
    99. End With
    100. End If
    101. End If
    102. Next j
    103. End If
    106. ' 2 und 3 Schicht
    107. If Cells(k, 28).Value = "x" And Cells(k, 29).Value = "x" Then 'in welcher Schich soll gearbeitet werden
    109. For j = sSpalte To sSpalte + 300
    110. If Cells(10, j) = "4" Then 'Suche nach dem nächsten Möglichen Starttermin in Schicht 1
    112. z = z + 1 'Anzahl der Schleifendurchläufe
    113. Cells(k, 15).Value = Cells(5, j).Value ' Schreibe die Startuhrzeit in Celle (k,15)
    114. If z = addiere + 1 Then
    115. Cells(k, 15).Value = Cells(5, j).Value
    116. Cells(k, 16).Value = Cells(7, j).Value
    118. If Cells(k + 1, 2) = "x" Then
    119. Cells(k + 1, 11) = Cells(k, 15)
    120. Cells(k + 1, 12) = Cells(k, 16).Value + TimeSerial(0, 30, 0)
    121. End If
    123. GoTo ort2 ' Steige aus wenn die Anzahl der anzahl der schleifen > Stunden +1
    124. End If
    125. If Cells(k, 32) = "2" Then 'Auf welcher Maschine wird gearbeitet
    127. Cells(16, j).Select
    128. Cells(16, j).Value = Cells(k, 1).Value ' Schreibe Positionsnummer in markiete Zelle brauche ich für ein Diagramm
    129. With Selection.Interior 'Färbe die Zelle farbig
    130. .ColorIndex = 5
    131. End With
    132. Cells(k, 15).Value = Cells(7, j).Value ' Schreibe das EndDatum in die Celle (k,15)
    133. Cells(k, 16).Value = Cells(7, j).Value ' Schreibe die EndUhrzeit in die Celle(k,16)
    136. ' Kommentar in die Zelle einfügen
    137. strCom = Cells(8, j).Value & Cells(k, 4).Value
    138. With Cells(16, j)
    139. .ClearComments
    140. .AddComment strCom
    141. End With
    142. End If
    143. End If
    144. Next j
    145. End If
    148. If Cells(k, 27).Value = "x" Then 'in welcher Schich soll gearbeitet werden
    150. For j = sSpalte To sSpalte + 300
    151. If Cells(9, j) = "1" Then 'Suche nach dem nächsten Möglichen Starttermin in Schicht 1
    153. z = z + 1 'Anzahl der Schleifendurchläufe
    154. Cells(k, 15).Value = Cells(5, j).Value ' Schreibe die Startuhrzeit in Celle (k,15)
    155. If z = addiere + 1 Then
    156. Cells(k, 15).Value = Cells(5, j).Value
    157. Cells(k, 16).Value = Cells(7, j).Value
    159. If Cells(k + 1, 2) = "x" Then
    160. Cells(k + 1, 11) = Cells(k, 15)
    161. Cells(k + 1, 12) = Cells(k, 16).Value + TimeSerial(0, 30, 0)
    162. End If
    164. GoTo ort2 ' Steige aus wenn die Anzahl der anzahl der schleifen > Stunden +1
    165. End If
    166. If Cells(k, 32) = "1" Then 'Auf welcher Maschine wird gearbeitet
    168. Cells(15, j).Select
    169. Cells(15, j).Value = Cells(k, 1).Value ' Schreibe Positionsnummer in markiete Zelle brauche ich für ein Diagramm
    170. With Selection.Interior 'Färbe die Zelle farbig
    171. .ColorIndex = 5
    172. End With
    173. Cells(k, 15).Value = Cells(7, j).Value ' Schreibe das EndDatum in die Celle (k,15)
    174. Cells(k, 16).Value = Cells(7, j).Value ' Schreibe die EndUhrzeit in die Celle(k,16)
    177. ' Kommentar in die Zelle einfügen
    178. strCom = Cells(8, j).Value & Cells(k, 4).Value
    179. With Cells(15, j)
    180. .ClearComments
    181. .AddComment strCom
    182. End With
    183. End If
    184. End If
    185. Next j
    186. End If
    191. End If
    192. End If
    193. Next
    194. End With
    195. ' MsgBox "Datum nicht gefunden"
    196. End If
    198. End If
    200. ort2:
    202. Next k
    204. Fehler:
    205. If Err.Number <> 0 Then
    207. Err.Clear
    208. End If
    210. Application.ScreenUpdating = True
    211. Application.Calculation = xlCalculationAutomatic 'automat.Berechnung einschalten
    213. MsgBox Timer - t & " sec", , "Makrolaufzeit"
    215. End Sub

    Dateien

    • Planung_neu.7z

      (1,09 MB, 174 mal heruntergeladen, zuletzt: )

    In Zeile 206 würde ich mal folgendes einsetzen:

    Visual Basic-Quellcode

    1. msgbox err.description, vbokonly+vbcritical, "Fehler:" & err.number


    Zwischendurch kannst Du Dir mittels debug.print Ausgaben in das Direktfenster geben lassen.

    Edit:
    Während der Entwicklung eines Makros schalte ich Fehler normal nicht ab. Wenn da einer auftaucht, kannst Du bequem in die entsprechende Zeile springen...
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „peterfido“ ()

Facebook
Telefonspende