Helpmij.nl
Helpmij.nl
Helpmij.nl
Steun Helpmij.nl! Klik hier     Computerprobleem? Klik hier!

Quote

Pagina 1 van 2 1 2 LaatsteLaatste
Weergeven resultaten 1 tot 20 van 21

Onderwerp: Macro Matcht geen gegevens, terwijl de critera wel voldoen

  1. #1
    Vraag is niet opgelost

    Macro Matcht geen gegevens, terwijl de critera wel voldoen

    Beste,

    ik zal het zo goed mogelijk uit proberen te leggen
    ik heb een lijst in tab 1. daarin staan een aantal referentienummers met bedragen en de datum waarop deze voorkomt
    in tab 2 staan dossiernummers, met referentienummers, begindatum en einddatum. Nu heb ik er 1 referentie uitgepikt voor beide tabs, maar er zijn in totaal meer dan 30.000 regels en 5000 dossiers, dit is slechts 1 referentie waar dit voorkomt van alle gevallen.

    wat de macro moet doen:
    de lijst 1 voor 1 af gaan in de eerste tab en kijken of de referentie (kolom E) voorkomt in tab 2 EN de waarde in tab 1 kolom F hetzelfde is als tab 2 kolom I EN tab 1 kolom F tussen de waardes ligt van tab 2 kolom K en L.
    ALS aan alle voorwaarden zijn voldaan (dus bedrag is 6,8, referentie is 1737309, en de datum in tab 1 ligt tussen 24-6-2018 en 31-10-2019) dan moet tab 2 kolom a (CRM20181683) worden neergezet in Tab 1 kolom T.

    voor slechts 2 regels vind ie ook iets, regel 45 en 57.. terwijl er (als ik naar de data kijk) er veel meer hits moeten zijn (als ik goed tel 196).

    nu is dit een kleine greep van de data die ik heb, en van een groot gros van de 30k referenties gaat het goed, maar 9000 regels niet, en dit is 1 referentie waarbij het dus niet goed gaat.
    mijn vraag is nu, wat gaat er fout, of wat heb ik verkeerd in de formule?

    ik heb de file toegevoegd in de bijlage. alvast bedankt voor de reacties!


    (ps. ik gebruik dus de dim Art en ML omdat de referentie 1737309 normaliter per regel anders kan zijn, daarmee gaat ie dus 1 voor 1 alle referenties af, en probeert ie dus te matchen met tab 2 (Masterlijst), dus normaliter dus meer dan 5000 dossiers heeft, in plaats van 1. ik heb ook geprobeerd om bij de vergelijkingsregels .value en .value2 erachter te zetten, maar dat hielp niet.. :/ de doEvents heb ik er tussen gezet omdat dit wat beter gaat dan zonder, omdat ik runs draai van 2000 regels per keer en excel steeds vast liep als ik deze er niet in zette)
    Bijgevoegde bestanden Bijgevoegde bestanden

  2. #2
    Giga Honourable Senior Member
    Geregistreerd
    2 maart 2013
    Wie of wat is tab2? Waar staan de dosiernummers? Waarom staat de data zo raar in de tab 'Provisiemodule'? Waarom staat de code in de module van ThisWorkbook?

    voor slechts 2 regels vind ie ook iets, regel 45 en 57.. terwijl er (als ik naar de data kijk) er veel meer hits moeten zijn (als ik goed tel 196).
    Dit zijn ook de enige regels waar iets ingevuld staat in kolom T

    Dus wat het het moet worden?
    Je kan een paard naar het water leiden, maar je kan het niet dwingen te drinken.

  3. #3
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    ik doe alles in geheugen en pas op het laatst wordt er teruggeschreven naar kolom T.
    Eerst maak ik een dictionary op basis van productcode en value, daar kunnen meerdere elementen inzitten blijkbaar.
    Dan in een loopje door je provisiemodule lopen en kijken of je creationdate tss begin en einde valt, zoja, de CMR erbij zetten.

    Ik vermoed dat voor 30k rijen en 5.000 dossiers, vrij snel moet afgewerkt zijn, enkele seconden ??
    Bijgevoegde bestanden Bijgevoegde bestanden

  4. #4
    Quote Origineel gepost door cow18 Bekijk Bericht
    ik doe alles in geheugen en pas op het laatst wordt er teruggeschreven naar kolom T.
    Eerst maak ik een dictionary op basis van productcode en value, daar kunnen meerdere elementen inzitten blijkbaar.
    Dan in een loopje door je provisiemodule lopen en kijken of je creationdate tss begin en einde valt, zoja, de CMR erbij zetten.

    Ik vermoed dat voor 30k rijen en 5.000 dossiers, vrij snel moet afgewerkt zijn, enkele seconden ??
    Top dit is precies wat ie zou moeten doen! precies idd de 196 regels die hij had moeten vinden (als ik de autofilter op de criteria zou aanpassen zou die dezelfde vinden).

    Nu is mijn vraag: waarom slaat de macro die ik geschreven heb er zoveel over? ligt het aan formatting van de cellen oid? (ik probeer te begrijpen waar en waarom het misgaat, gezien er alleen 2 rijen gematcht werden terwijl er 196 gematcht zouden moeten worden.. ik snap het niet )

  5. #5
    Quote Origineel gepost door VenA Bekijk Bericht
    Wie of wat is tab2? Waar staan de dosiernummers? Waarom staat de data zo raar in de tab 'Provisiemodule'? Waarom staat de code in de module van ThisWorkbook?


    Dit zijn ook de enige regels waar iets ingevuld staat in kolom T

    Dus wat het het moet worden?
    Hoi Vena, er zijn maar 2 regels gevuld door de macro, terwijl het er 196 hadden moeten zijn. de macro zou het de referentie op moeten zoeken in de lijst, kijken of creation date in de range valt van de datum die er achter staat, en het bedrag overeenkomt (die 8,60) en als dit zo is, het bijbehorende compensatienummer (dus kolom 1 van de tab Masterlijst, wat 1 enkele referentie is van alle referenties in "Blad2") moeten kopieren naar kolom T.

    Ik snap echter niet waar het mis gaat, want net als de 2 regels die binnen de criteria vallen, zijn er nog 194 andere regels die er in zouden moeten vallen, alleen negeert de macro deze regels lijkt het :s

  6. #6
    update: in mijn file ligt het waarschijnlijk aan kolom P in de tab provisiemodule gok ik. als ik namelijk de 6,8 in rij 45 naar de overige rijen van die kolom kopieer, worden de 196 rijen wel gematcht. Weet iemand misschien wat er met de rijen 45 en 57, kolom T van provisiemodule, anders is dan met de rest van de rijen? ik kan geen verschil vinden... :s

  7. #7
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    gek, voeg in else in die if... then constructie in om het verschil tussen beide 6.8 te vinden en je krijgt een afwijking van 3E-15, een miniscule afwijking binnen excel
    Ik begrijp ook eventjes niet waar die vandaan komt.
    Code:
    Else
                    MsgBox Worksheets("Provisiemodule").Range("P" & Art) - Worksheets("Masterlijst").Range("I" & ML)
                End If
    Vanwaar kwamen die getallen, uit een ander bestand of zo ?

  8. #8
    Quote Origineel gepost door cow18 Bekijk Bericht
    gek, voeg in else in die if... then constructie in om het verschil tussen beide 6.8 te vinden en je krijgt een afwijking van 3E-15, een miniscule afwijking binnen excel
    Ik begrijp ook eventjes niet waar die vandaan komt.
    Code:
    Else
                    MsgBox Worksheets("Provisiemodule").Range("P" & Art) - Worksheets("Masterlijst").Range("I" & ML)
                End If
    Vanwaar kwamen die getallen, uit een ander bestand of zo ?
    ja die getallen komen uit een datadump/export van een Microsoft Dynamic systeem, die was in tabelvorm die ik heb geconverteerd naar bereik, daarna gekopieerd en geplakt als waarden.

  9. #9
    zou iemans de onderstaande regel kunnen uitleggen?
    If WorksheetFunction.Median(CLng(sn(i, 6)), CLng(s1(1)), CLng(s1(2))) = CLng(sn(i, 6)) Then 'creation ligt tussen begin en einddatum
    sn(i, 20) = s1(0) 'CRM overnemen in 20e kolom


    de volgende stap die ik wil checken is of er referenties zijn met een creation date na 31-10-2018, maar wel voor hetzelfde artikelreferentie en dan weer het dossiernummer in kolom T van de provisiemodule plakken.
    Ik kan echter niet zo goed volgen hoe de median functie hier de data weerspiegelt en dus wat ik zou moeten aanpassen..
    alvast bedankt!

  10. #10
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    ik had aanvankelijk in die dictionary alle gegevens opgeslagen wat je op blad2 had weergegeven, nl. CRM, begin en einddatum, respectievelijk s1(0), s1(1) en s1(2).
    Beide laatsten als long variable, dus zonder cijfers achter de komma, zodoende had ik niet het probleem van afwijking van 3E-15.
    worksheetfunction.Median is dezelfde functie als de mediaan op werkbladniveau, daarmee check je met 3 getallen of een bepaald getal binnen het bereik van de kleinste en de grootste van die 3 getallen ligt.
    Dus kort door de bocht, of de F-kolom van provisie binnen het bereik van de K en L-kolom van je 2e tabblad ligt.



    Wat begrijp je nog niet van mijn macro ?
    Laatst aangepast door cow18 : 17 mei 2019 om 12:23

  11. #11
    Quote Origineel gepost door cow18 Bekijk Bericht
    ik had aanvankelijk in die dictionary alle gegevens opgeslagen wat je op blad2 had weergegeven, nl. CRM, begin en einddatum, respectievelijk s1(0), s1(1) en s1(2).
    Beide laatsten als long variable, dus zonder cijfers achter de komma, zodoende had ik niet het probleem van afwijking van 3E-15.
    worksheetfunction.Median is dezelfde functie als de mediaan op werkbladniveau, daarmee check je met 3 getallen of een bepaald getal binnen het bereik van de kleinste en de grootste van die 3 getallen ligt.
    Dus kort door de bocht, of de F-kolom van provisie binnen het bereik van de K en L-kolom van je 2e tabblad ligt.



    Wat begrijp je nog niet van mijn macro ?
    Ah dan snap ik het. Ik wist even niet wat de s1(0), (1) en (2) waren.

    Dan snap ik het.

    Ik ga mij eens verdiepen in het gebruiken van een dictionary, dat gaat vele malen sneller dan loopen door regels (ca. 1 seconde per regel in dit geval.. pff).

    Bedankt cow18!!

  12. #12
    @cow18: in welk stukje code worden de s1(0) s1(1) en s1(2) gedefinieerd dat dit de CRM dossiernummers, begin, en einddatum zijn? ik kom er even niet uit...

  13. #13
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    per sleutel (combinatie van productcode en waarde) wordt er een string verzamelt met die CRM, begin- en einddatum.
    Er kunnen meerdere zulke zaken per sleutel voorkomen, die worden gescheiden door een "|" en intern worden die gescheiden door een "/".
    Dus zie je daar 2 keer een split staan, waarvan de 2e in een For ... Next loop staat
    Nog een weetje, door die split krijg je als resultaat strings (=tekst) terug, dus moet je met Clng werken om die begin en einddatums weer getallen te krijgen.
    Code:
                    s = Split(dict(sleutel), "|")                        'waarde splitten op "|"
                    For j = 1 To UBound(s)                               'van de 2e tot laatste record aflopen
                        s1 = Split(s(j), "/")                            'record splitten op "/"
    Om die dictionary en bovenstaande nog beter te begrijpen kan je als laatste regel aan die macro nog onderstaande toevoegen
    Code:
       
        Sheets("blad2").Range("AA1").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))
    Daarmee schrijf je de inhoud van de dictionary (= keys en items) weg naar AA1 en volgende van blad2
    Scroll daar nu eens door :
    - in de AA-kolom zie je netjes die productcode en die waarde. Die laatste is normaal 1 of 2 cijfers na de komma, maar zo nu en dan zie je er ééntje met wel 5 cijfers of meer na de komma.
    Klopt dat in je ogen wel, of is dat een foutje in je geciteerde vertaalslag ?
    Anders, met een regeltje erbij, houden we bv. alleen rekening tot bv. 5 cijfers na de komma
    - In de AB-kolom zie je meestal 1 maar in een uitzonderlijk geval wel een keer 6 CRM, begin en einddatum-combinaties staan, zoals hierboven beschreven en dus gescheiden door "|" en "/"
    Laatst aangepast door cow18 : 17 mei 2019 om 17:11

  14. #14
    Quote Origineel gepost door cow18 Bekijk Bericht
    per sleutel (combinatie van productcode en waarde) wordt er een string verzamelt met die CRM, begin- en einddatum.
    Er kunnen meerdere zulke zaken per sleutel voorkomen, die worden gescheiden door een "|" en intern worden die gescheiden door een "/".
    Dus zie je daar 2 keer een split staan, waarvan de 2e in een For ... Next loop staat
    Nog een weetje, door die split krijg je als resultaat strings (=tekst) terug, dus moet je met Clng werken om die begin en einddatums weer getallen te krijgen.
    Code:
                    s = Split(dict(sleutel), "|")                        'waarde splitten op "|"
                    For j = 1 To UBound(s)                               'van de 2e tot laatste record aflopen
                        s1 = Split(s(j), "/")                            'record splitten op "/"
    Om die dictionary en bovenstaande nog beter te begrijpen kan je als laatste regel aan die macro nog onderstaande toevoegen
    Code:
       
        Sheets("blad2").Range("AA1").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))
    Daarmee schrijf je de inhoud van de dictionary (= keys en items) weg naar AA1 en volgende van blad2
    Scroll daar nu eens door :
    - in de AA-kolom zie je netjes die productcode en die waarde. Die laatste is normaal 1 of 2 cijfers na de komma, maar zo nu en dan zie je er ééntje met wel 5 cijfers of meer na de komma.
    Klopt dat in je ogen wel, of is dat een foutje in je geciteerde vertaalslag ?
    Anders, met een regeltje erbij, houden we bv. alleen rekening tot bv. 5 cijfers na de komma
    - In de AB-kolom zie je meestal 1 maar in een uitzonderlijk geval wel een keer 6 CRM, begin en einddatum-combinaties staan, zoals hierboven beschreven en dus gescheiden door "|" en "/"
    het klopt dat er idd een paar referenties zijn met meerdere cijfers achter de komma, zat in de data.

    ah ik snap het, ik had even niet door dat de 3 variabelen later werden aangemaakt.

    nu heb ik nog een vraag. stel dat ik de formule wil aanpassen om te zoeken naar artikelnummer, bedrag en groter dan de einddatum (in plaats van tussen begin en einddatum), hoe kan ik dit het beste wijzigen?

  15. #15
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    Code:
     For j = 1 To UBound(s)                               'van de 2e tot laatste record aflopen
                        s1 = Split(s(j), "/")                            'record splitten op "/"
                        'If WorksheetFunction.Median(CLng(sn(i, 6)), CLng(s1(1)), CLng(s1(2))) = CLng(sn(i, 6)) Then    'creation ligt tussen begin en einddatum
                        If CLng(sn(i, 6)) >= CLng(s1(2)) Then   'creation ligt na einddatum
       
    vervang de 1e rode regel door de 2e rode regel
    Voor de 1e rode regel staat er nu een enkel haakje waardoor die comment werd.

    Nog een opmerking in de marge, mijn vorige opmerking volledig citeren, dat wordt algemeen een beetje al hinderlijk aanzien, vooral als het gaat over een reactie er net voor.
    Gewoon reageren is voldoende.
    Laatst aangepast door cow18 : 20 mei 2019 om 10:26

  16. #16
    Top, het werkt soepeltjes! Bedankt!
    Ik ga de code bestuderen en kijken waar ik dit nog meer voor zou kunnen inzetten. Nogmaals bedankt voor deze snelle oplossing! Gaat echt gigantisch veel sneller dan de loop die ik had.. super!

  17. #17
    Vraagje, is het mogelijk om sum ifs in vba te doen met behulp van zo een dictionary?
    Momenteel heb ik een bestand die sumifs gebruikt (som kolom x als datum a tussen datum b en c ligt, en artikel D gelijk is aan x en afdeling gelijk is aan y) als formule in het werkblad, maar als het veel regels zijn gaat het "berekenen" heel traag. Dus als het via zo een dictionary sneller kan...

  18. #18
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    als dergelijke zaken traag beginnen te werken, dan moeten er eventueel een aantal alarmbellen afgaan.
    - gebruik je niet ergens overdadig veel voorwaardelijke opmaken
    - zijn de bereiken van die sumifs of sumproducts niet heel ruim gekozen
    - heb je matrixformules in gebruik

    Dus als het niet aan bovenstaande ligt, dan kan je het met zo'n dictionary proberen, maar is een draaitabel ook geen optie ?
    Die laatste is zonder macro.
    Voordeel van beiden is dat je ganse werkblad niet herrekend wordt, op het ogenblik dat je ergens een cel wijzigt, maar op jouw aanvraag.

  19. #19
    Hoi cow18,

    qua opmaken zijn er niet zo veel, 1 tab met een tabel met data er in vanuit Microsoft Dynamics, 1 tab met een datadump uit een andere bron (SAP Business Objects volgens mij heet het officieel).

    normaliter gaat het vrij vlot, binnen een paar tellen is alles berekend. Het probleem is alleen, als ik een filter op de data zet, dat deze soms sporadisch gaat herberekenen. Meestal is het geen probleem, maar er zitten momenten tussen dat er meer dan 10k regels zijn die dan elke keer worden herberekend.. dan wordt het al snel traag.

    vandaar dat ik dacht, misschien is dit te voorkomen door een dictionary te gebruiken. Zal ik een dummybestand hiervan posten? Misschien schept dat een iets beter beeld.

  20. #20
    Mega Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    een dummy-bestand mag altijd, alhoewel 10k rijen, dan zal je die vermoedelijk moeten zippen, probleempje met de bestandsgrootte.
    Laatst aangepast door cow18 : 23 mei 2019 om 08:49

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl en business

Partners
Sponsoren