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)

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).

Klik om te vergroten...

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

Dus wat het het moet worden?

cow18 zei:

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 ??

Klik om te vergroten...

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 )

VenA zei:

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?

Klik om te vergroten...

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

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

cow18 zei:

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 ?

Klik om te vergroten...

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.

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!

cow18 zei:

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 ?

Klik om te vergroten...

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!!

@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...

cow18 zei:

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 "/"

Klik om te vergroten...

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?

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!

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...

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.

Het is gelukt!

@ cow18: bedankt voor alle hulp en tips, waardeer het enorm!