Help!

PC-Problemen?
De vrijwilligers van Oplossing.be zoeken gratis met u mee!

Hulp bij posten

Recente topics

Auteur Topic: datums vergelijken  (gelezen 9519 keer)

0 leden en 1 gast bekijken dit topic.

Offline Frans1949

  • Lid
  • *
  • Berichten: 64
  • Geslacht: Man
  • Oplossing.be
datums vergelijken
« Gepost op: 04 januari 2021, 23:19:56 »
Hallo,

Ik probeer een datum op blad1 te vergelijken met verschillende datums in blad2, kolom B
Indien de datum op blad2 niet gelijk is aan de datum in blad1, cel B1,
dan dienen alle rijen in blad2 die niet gelijk zijn aan de datum in blad1. cel B2 verwijderd te worden.
Met onderstaande code lukt het wel maar dan moet ik iedere keer de datum handmatig aanpassen.

Zie ook bijgevoegd voorbeeld.

Frans
Sub Macro1()
Dim I As Long
Dim rng As Range
With ActiveWorkbook.Sheets("Blad2")
   For I = 100000 To 1 Step -1
         With .Cells(I, "B")
            If .Value <> "01-12-2020" Then
                If rng Is Nothing Then
                     Set rng = .Cells
                 Else
                    Set rng = Application.Union(rng, .Cells)
               End If
              End If
         End With
     Next I
     If Not rng Is Nothing Then rng.EntireRow.Delete
 End With
End Sub

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.128
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: datums vergelijken
« Reactie #1 Gepost op: 04 januari 2021, 23:36:40 »
Hallo Frans1949,

ik ben maar een Excel-novice, maar je macro werkt toch niet zoals bedoeld in je uitleg.
Niet enkel de rijen in Blad2!"B:B"  waarvan de datum niet overeenkomt worden verwijderd, maar ook de rijen waarvan de datum wel overeenkomt, dus... alles.

Tweede vraag die ik me "als beginneling" stel is wat je verwacht van de macro als je zegt: "...maar dan moet ik iedere keer de datum handmatig aanpassen". Dit kan toch enkel de bedoeling zijn dat je of voorwaarden voor de datum ingeeft in de macro (bvb elke datum op een maandag, in januari, of elke 25 ste enzovoort) ....of anders moet je het toch zelf aangeven (in Blad1! B1 oid), denk ik zo...  :-\

Groeten,

:) SoftAid :)             


Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline Frans1949

  • Lid
  • *
  • Berichten: 64
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #2 Gepost op: 04 januari 2021, 23:44:09 »
Softaid,

Vreemd, als ik de knop macro gebruik op blad1 dan wordt alles verwijderd in kolom B.
behalve de rijen waar 01-12-2020 instaat.

Frans

Offline cow18

  • Ervaren lid
  • ***
  • Berichten: 340
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #3 Gepost op: 05 januari 2021, 06:19:18 »
de macro kan natuurlijk efficienter geschreven worden, bv. als een autofilter, maar dat ter zijde.
Je moet nadenken over wat je vergelijkt met elkaar.
In je macro staat er een tekst, dus een string, nl. "01-12-2020", die enkel gevoelsmatig lijkt op een datum en in blad2 kolom B staan echte datums en dat zijn (gehele, dus type Long of Integer) getallen. Logisch dat alles gewist wordt.
In onderstaande staan er 3 "If"-constructies waarbij er telkens maar 1 gebruikt mag worden, te zien aan dat enkel aanhalingsteken er voor. Probeer ze alle 3 eens en schrijf eens op wat de msgbox zegt. Voorlopig worden de regels niet verwijderd, dus moet je die niet terugkopieren van je reservekopie.
Wat zijn je bevindingen ?
In principe is die Clng(..) in de 1e if overbodig, want het was al een geheel getal.
 
Sub Macro1()
   Dim I       As Long
   Dim rng     As Range

   t = Timer
   With ActiveWorkbook.Sheets("Blad2")
      For I = 100000 To 1 Step -1
         With .Cells(I, "B")
            [color=red]If .Value <> CLng(Sheets("blad1").Range("B1").Value) Then   'vergelijken met de cel B1 van blad1 als Long-variable
            'If .Value <> DateSerial(2020, 12, 1) Then   'vergelijken met die datum (is direct een geheel getal)
            'If .Value <> "01-12-2020" Then   'vergelijken met een tekst die op een datum "lijkt"[/color]

               If rng Is Nothing Then
                  Set rng = .Cells
               Else
                  Set rng = Application.Union(rng, .Cells)
               End If
            End If
         End With
      Next I

      If Not rng Is Nothing Then
         'rng.EntireRow.Delete
         MsgBox rng.Address & vbLf & Timer - t
      End If
   End With

End Sub

Aanpassing door moderator: [quote...  vervangen door [code...

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: datums vergelijken
« Reactie #4 Gepost op: 05 januari 2021, 10:22:16 »
Frans,

De uitleg die je er op Blad1 hebt bijgeschreven komt niet overeen met wat in je macro staat. Daar heeft cow18 al op gewezen.

Maar zowel op Blad2 als in B1 van Blad1 staan wél echte datums, en dan kan je verder met de suggestie van SoftAid.
Het autofilter-voorstel van cow18 heb ik even aan de kant gelaten, niet omdat het niet goed zou zijn, maar omdat ik heb geprobeerd een zo begrijpelijk mogelijke code te schrijven.
Het selecteren van Blad2 op het einde is enkel relevant om je een idee te geven dat je eigen code nogal traag was, maar dat mag je natuurlijk weghalen.

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline cow18

  • Ervaren lid
  • ***
  • Berichten: 340
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #5 Gepost op: 05 januari 2021, 10:54:28 »
gelukkig neemt pitufo maar de 19 aanwezige rijen mee en komt hij daardoor scherp uit de hoek   ;)
Zet anders achtereenvolgens eens iets in B1000 of B10000 of B100000 en ga bij die laatste een bak koffie drinken, moet niet speciaal zo'n senseo zijn.
Met een autofilter en de volle 100.000 rijen (alhoewel een beetje onzinnig), duurt dat grootte-orde 0.1 sec  ::)

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: datums vergelijken
« Reactie #6 Gepost op: 05 januari 2021, 11:09:03 »
@ cow18,

Dat is toch wat ik zelf ook al zei
Citaat
Het autofilter-voorstel van cow18 heb ik even aan de kant gelaten, niet omdat het niet goed zou zijn, maar omdat ik heb geprobeerd een zo begrijpelijk mogelijke code te schrijven
Ik heb m.a.w. met opzet de denkwijze van TS gevolgd, meer bepaald omdat ik me nog goed een ander draadje van hem herinner...
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Frans1949

  • Lid
  • *
  • Berichten: 64
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #7 Gepost op: 05 januari 2021, 11:27:34 »
@Pitufo en @Koe,

Beide macro,s van jullie, in het voorbeeld bestand, doen wat ik wilde.

Ga het vanavond in mijn originele bestand uitproberen.

Jullie horen nog.

Alvast bedankt.

Frans

Offline cow18

  • Ervaren lid
  • ***
  • Berichten: 340
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #8 Gepost op: 05 januari 2021, 11:35:17 »
inderdaad iets herlezen en de 2e indruk geven soms een ander inzicht, mea culpa  _/-\o_

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: datums vergelijken
« Reactie #9 Gepost op: 05 januari 2021, 15:11:33 »
Don't worry, samen over iets nadenken kan soms iedereen ten goede komen...

Je hoort me al komen, iets zei me namelijk dat
Citaat
Met een autofilter en de volle 100.000 rijen duurt dat grootte-orde 0.1 sec
wel eens een héél optimistische inschatting zou kunnen zijn.

Laat mijn pc'tje dan aan de trage kant zijn, de verhoudingen zullen allicht betrouwbaar zijn.

Welnu, ik heb er effectief 100.000 rijen van gemaakt en dan duurt de autofilter-methode 46 seconden (mijn eerdere niet getest, want het was toen al duidelijk dat die inderdaad geschikt zal zijn voor een koffiebreak). Het is, volkomen logisch, "SpecialCells(xlVisible).EntireRow.Delete" dat het boeltje vertraagt.

Omdat vba nu eenmaal plezant is heb ik een volledig afwijkend alternatief gebouwd (met a priori het geloof in beterschap  0:-) ), en jawel: 0,6 seconden (mijn hoop situeerde zich tussen 5 en 10)
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline cow18

  • Ervaren lid
  • ***
  • Berichten: 340
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #10 Gepost op: 05 januari 2021, 16:10:45 »
 \o/
Dan komt het er op aan te kijken waar de verschillen zitten.

Die 46 sec bij jou zijn er 16 bij mij nadat ik die kolom B van reserveblad2 gekopieerd had.
Dus die wat oudere PC van jou, moet je die nog met een hendel of zoiets aan de buitenkant op snelheid brengen  :P

Dan heb ik even voor de hein die kolom B van reserveblad2 gekopieerd naar 2 kolommen ernaast en vooraf al gesorteerd vooraleer die naar blad2 te kopieren. Resultaat ? Ook jouw 0.65 sec worden 0.16 sec, dus ook in grootte-orde 3-4 keer sneller.

Je ingeving van voorafgaand te sorteren zal er dus wel toe doen, maar je zal wel moeten over het ganse bereik sorteren ipv enkel kolom B.

Eigenlijk een leuke en pittige discussie.

Nu ben ik wel geen echte techneut, maar dit is hem en verder moet je me niet lastig vallen met moeilijke vragen, op dat gebied ben ik een flauwe plezante.
 

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: datums vergelijken
« Reactie #11 Gepost op: 05 januari 2021, 16:45:13 »
Citaat
die wat oudere PC van jou, moet je die nog met een hendel of zoiets aan de buitenkant op snelheid brengen
Toch niet, hij moet continu ondersteund worden, dus 'k heb er gewoon de pedalen van oma's antieke naaimachine aangelast.

Citaat
dit is hem en verder moet je me niet lastig vallen met moeilijke vragen
Dan durf ik ook geen reactie meer verwachten, maar wil er wel nog, het zal dan vooral voor TS en/of toevallige voorbijgangers zijn, een woordje aan besteden.

Uiteraard dient er op de ganse range gesorteerd te worden, maar in het voorbeeld bleef die beperkt tot één kolom.
Het grote tijdsverschil zit hem in het individueel rijen verwijderen t.o.v. in volledige blokken, daarom is sorteren de boodschap.
En ook dan hoeft het niet per se deze werkwijze te zijn. Je zou (om maar wat te zeggen, want er zijn elfendertig manieren te bedenken) na het sorteren de te behouden range in een variabele kunnen stoppen, dan het blad legen, en de variabele plakken.
Alleen zal daar de grote winst niet meer te boeken zijn: ik heb er nog eens 1.000.000 rijen van gemaakt en dat duurde net geen 4 seconden.
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Frans1949

  • Lid
  • *
  • Berichten: 64
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #12 Gepost op: 07 januari 2021, 00:10:35 »
Hallo,

2 macro,s(Cow en pitufo_speedy) geven een foutmelding als er een datum is ingevoerd die niet op blad2 voorkomt.
Zie geel gearceerde.
Bij een juiste datum zijn ze wel een stuk sneller, als je met je ogen knippert is het gebeurd.


Macro1 geeft geen fout melding, maak alleen blad2 leeg.
Is wel traag, het zijn hooguit 800 rijen, doet daar circa 7 seconden over.
Dat blad2 leeg gemaakt wordt is geen probleem, want dan worden op blad1 met een vervolg macro enkele kolommen niet ingevuld.
En middels een msgbox kan ik dan wel aangeven het juiste bestand te kiezen of de juiste datum in te voeren.


PS Fouten staan wel tussen           .
Maar worden niet geel gearceerd.
Frans

Sub Macro1()

   Application.ScreenUpdating = False
   t = Timer
   datum = Blad1.Cells(1, 2)
   With Blad2
      For I = .Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1   'gelukkig maar 19 rijen !!!!
         If .Cells(I, 2) <> datum Then .Rows(I).Delete
      Next I
      .Select
   End With
   MsgBox Timer - t
End Sub
''XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Sub cow()
   Application.ScreenUpdating = False
   datum = CLng(Sheets("blad1").Range("B1").Value)
   t = Timer
   With Sheets("blad2")
      .AutoFilterMode = False
      .Rows(1).Insert                            'bovenin rij toevoegen
      With .Range("B1").Resize(100001)           'de volle 100.000+1 rijen
      .Cells(1).Value = "B1"                  'kop er in zitten
         .NumberFormat = "0"                     'getallen ipv datums[color=yellow][/color]
         .AutoFilter 1, "<>" & datum   'filter
         .SpecialCells(xlVisible).EntireRow.Delete   'alle zichtbare rijen wissen
         [color=yellow].NumberFormat = "dd/mm/yyyy" [/color]           'terug naar datumopmaak
      End With
   End With
   MsgBox Timer - t
End Sub
''XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Sub pitufo_speedy()

kopieren
t = Timer
Application.ScreenUpdating = False
datum = Blad1.Cells(1, 2)
With Sheets("blad2")
    .Columns(2).Sort key1:=.Cells(1, 2), Header:=xlNo
    .Rows(1).Insert
    rijen = .Cells(Rows.Count, 2).End(xlUp).Row
    juiste = WorksheetFunction.CountIf(.Columns(2), datum)
    [color=yellow]eerste_juiste = .Columns(2).Find(what:=datum).Row[/color]
    eerste_foute = eerste_juiste + juiste
    If eerste_foute < rijen Then
        .Rows(eerste_foute & ":" & rijen).Delete
    End If
    If eerste_foute > 1 Then
        .Rows("1:" & eerste_juiste - 1).Delete
    End If
End With
MsgBox Timer - t

End Sub

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: datums vergelijken
« Reactie #13 Gepost op: 07 januari 2021, 09:39:30 »
Frans,

Dat Blad2 wordt leeg gemaakt als de zoekdatum er niet voorkomt is toch wat je zelf vroeg:
Citaat
Indien de datum op blad2 niet gelijk is aan de datum in blad1, cel B1,
dan dienen alle rijen in blad2 die niet gelijk zijn aan de datum in blad1. cel B2 verwijderd te worden.

Waarom mijn eerste macro niet van de snelste is heb ik al meermaals uitgelegd, maar hij is waarschijnlijk nog een stuk sneller dan wat je zelf had gefabriceerd?

In mijn vervolgmacro toonde ik aan dat de filter-methode waar iedereen spontaan naar grijpt niet alleenzaligmakend hoeft te zijn. Als die bij jou even snel is als mijn 'speedy'-methode zijn je datums vermoedelijk al op voorhand gesorteerd, en dan wordt de keuze irrelevant. Maar op zich was dat spielerei tussen cow18 en mezelf, dus onmogelijk te beschouwen als definitieve oplossing (bij gebrek aan volledige info van jouw kant).

De mogelijkheid dat een zoekdatum helemaal niet zou voorkomen op Blad2 is iets waar ik in mijn tweede macro inderdaad geen rekening mee had gehouden, en cow18 waarschijnlijk ook niet, want anders zou je moeilijk foutmeldingen kunnen krijgen.
Dat in de voorbeeldcodes aanpassen is enkele seconden typwerk (denkwerk noem ik dat niet). Maar het waren dus voorbeeldcodes voor iets waarvan we zelf ook wel aanvoelden dat je echte bestand er anders zou uitzien. Dan weet je zo dat de puntjes nog op de i zullen gezet moeten worden, niet?

Dus bezorg eerst maar eens een representatief voorbeeld (ontdaan van reële persoonsgegevens), anders zullen we weer nog meermaals moeten aanpassen.

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Frans1949

  • Lid
  • *
  • Berichten: 64
  • Geslacht: Man
  • Oplossing.be
Re: datums vergelijken
« Reactie #14 Gepost op: 07 januari 2021, 10:29:08 »
Pitufo,

Ik probeerde alleen maar aan te geven wat er, bij de 2 van de 3 macro,s, fout gaat als er in blad1 een datum wordt ingevuld die in blad2 niet voorkomt.
Bij de voor mij juist werkende macro1 geef ik toch aan, dat blad2 leeg gemaakt wordt en dat dat geen probleem is.

Citaat
Dat blad2 leeg gemaakt wordt is geen probleem, want .......

Frans

 


www.combell.com