Lengtes berekenen voor min en max

[NogGeenGuru]

Hallo allemaal,

Ik heb een tooltje gemaakt die lengtes bij elkaar optelt.
Ter verduidelijking heb ik een voorbeeld bestand bij gevoegd.

In kolom B staat de lengte, in kolom C een laag nummer (min), In kolom D een hoog nummer (max).
De lengte in B hoort bij alle nummers van laag t/m hoog.

In de cellen daarnaast staat in het geel nummer 1 t/m 10. Normaal gesproken is dit 1 t/m 900 maar ik heb het voorbeeld bestand kleiner gemaakt i.v.m. de maximale grootte om te uploaden op het forum.
als de gele cel in tussen de getallen in kolom C en D valt dan moet de lengte (in B) bij dit nummer opgeld worden.

het tooltje werkt op dit moment. maar in het echte bestand zijn er 24000 lengtes onder elkaar gezet.
als ik dit maak dan loopt excel vast.
Ik denk dat er dan zo veel forumles staan dat excel het niet meer trekt.

weet iemand een manier om dit te doen die wat minder geheugen in beslag neemt?

 

[alphamax]

In F3

=ALS(EN(F$2>=$C3;F$2<=$D3);$B3;0)

En daarna doorvoeren.

Of met "omgekeerde" logica in F3

=ALS(OF(F$2<$C3;F$2>$D3);0;$B3)

 

[Ginger]

Ik heb 'm in een kleine macro voor je gegoten. Let wel ff op dat ik je outputtabel 1 kolom naar rechts heb geschoven. Dit moet omdat de code anders je brontabel niet goed kan bepalen...Bekijk bijlage Lengte berekenen voorbeeld.zip

 

[NogGeenGuru]

wow super bedankt allebei

Ginger, zou je mij ook kunnen uitleggen hoe jou macro precies werkt?
In het volledige bestand staan de cellen namelijk op andere plaatsen. hoe kan ik de macro aan passen naar die indeling?
Ik zie nergens dat je een cel hebt aan gegeven

 

[Ginger]

In het volledige bestand staan de cellen namelijk op andere plaatsen.

Aiii... Het is altijd van belang om in een voorbeeld de structuur van het origineel aan te houden. Dan is de gegeven oplossing ook écht "pasklaar".

Maar goed... Daar gaan we...

Sub MaakLengtes()

02    BronTabel = ActiveSheet.Cells(2, 2).CurrentRegion
03    ReDim OutputTabel(1 To UBound(BronTabel, 1) - 1, 1 To 900)
    
04    For i = 2 To UBound(BronTabel, 1)
05        For ii = BronTabel(i, 2) To BronTabel(i, 3)
06            OutputTabel(i - 1, ii) = BronTabel(i, 1)
07        Next ii
08    Next i
    
09    With ActiveSheet.Cells(3, 7).Resize(UBound(OutputTabel, 1), UBound(OutputTabel, 2))
10        .ClearContents
11        .Value = OutputTabel
12    End With
    
End Sub

In regel 02 wordt vanaf cel B2 (in de code is dat Cells(2, 2)) de "CurrentRegion" opgehaald in een array. Deze CurrentRegion is een gebied van cellen dat wordt omsloten door een lege kolom en lege regel. Daarom had ik die outputtabel een kolom naar rechts geplaatst. Maar door deze CurrentRegion hoef je verder dus niet meer uit te zoeken hoe groot dat gebied is. Dat wordt allemaal voor je geregeld.
In regel 03 maak ik de array aan van de output. Deze heeft net zoveel regels als de BronTabel maar daarnaast wél 900 kolommen.
In regel 04 wordt bepaald hoeveel regels de brontabel heeft en dat er vanaf record 2 in array moet worden begonnen (record 1 is de header van die tabel namelijk).
In regel 05 wordt bepaald wáár in de output array de gegevens moeten worden geplaatst: BronTabel(i, 2) To BronTabel(i, 3). De inhoud van BronTabel(i, 2) is de min waarde en de inhoud van BronTabel(i, 3) is de max waarde. Dit wordt dus in de lus afgegeven aan de variabele ii.
In regl 06 wordt de outputtabel gevuld met de eerste waarde van het record van de brontabel.

In regel 09 geef ik dmv Cell(3,7) aan dat vanaf cel G3 een bereik moet worden ingesteld die net zoveel regels en kolommen heeft als de array OutputTabel: Resize(UBound(OutputTabel, 1), UBound(OutputTabel, 2))
In regel 10 worden eerst ff de oude gegevens uit de OutputTabel verwijderd. Je wilt er immers geen foute gegevens van een vorige run in hebben staan.
In regel 11 wordt het bereik dat in regel 09 is aangemaakt, gevuld met de inhoud van de array OutputTabel.

Door het gebruik van Array's in de procedure wordt alles in het werkgeheugen afgehandeld en dus niet meer in de sheet zelf. De enige interface is die bij het ophalen van de brongegevens en wegschrijven van de outputgegevens. Jouw bron met 24000 records zou dus geen probleem moeten opleveren en binnen enkele secondes klaar zijn.

Ik hoop dat je hiermee zelf aan de slag kan in het originele bestand. Let in dit geval ook wel ff héél goed op mijn onderschrift in m'n bericht!!!
Zo niet, dan een bijlage posten die meer lijkt op dat origineel....

 

[NogGeenGuru]

Beste ginger,

Bedankt voor de uitleg. Ik ga mijn best doen om de code aan te passen naar mijn bestand.
Ik wou in eerste instantie ook het originele document plaatsen. Maar deze was te groot (te veel mb's) om te uploaden. Dus moest ik hem inkrimpen tot wat er over bleef.

 

[NogGeenGuru]

ginger, ik ga toch je hulp nodig hebben...
het enige verschil met het voorbeeld bestandje en het echte bestandje is de positie.
in het voorbeeld bestandje staat het woord "lengte" in B2. in werkelijkheid is dit AE2.
Het eerste (geelgekleurde) cijfer staat in AJ2.

Ik dacht dat cell(2, 2) dan veranderd moest worden in cell(2, 31), de 2de regel in de 31ste kolom.
en AJ wordt dan Cell(2, 36) met dezelfde logica. maar ik blijf fout meldingen krijgen.

Het laatste verschil is het aantal recoords maar die zouden toch geen probleem op leveren zei je?

Sub MaakLengtes()

    BronTabel = ActiveSheet.Cells(2, 31).CurrentRegion
    ReDim OutputTabel(1 To UBound(BronTabel, 1) - 1, 1 To 900)
    
    For i = 2 To UBound(BronTabel, 1)
        For ii = BronTabel(i, 31) To BronTabel(i, 31)
            OutputTabel(i - 1, ii) = BronTabel(i, 1)
        Next ii
    Next i
    
    With ActiveSheet.Cells(3, 36).Resize(UBound(OutputTabel, 1), UBound(OutputTabel, 2))
        .ClearContents
        .Value = OutputTabel
    End With
    
End Sub

dit is wat ik ervan gemaakt heb... de fout melding geeft de regel aan die begind met "For ii =" maar ik begrijp niet wat er fout gaat

Ik kan helaas geen voorbeeld bestandje uploaden want zelfs al voeg ik alleen cellen in bij jouw uitwerking overschrijt het bestandje de 100mb.

 

[alphamax]

For ii = BronTabel(i, 31) To BronTabel(i, 31)

moet blijven

For ii = BronTabel(i, 2) To BronTabel(i, 3)

 

[Ginger]

@alphamax, Tnx!

@NogGeenGuru, Nog een stukje uitleg?
De variabele "BronTabel" is een Array. Je hebt dus de gegevens uit de cellen (= bereik) van de sheet in een stuk geheugen gepropt. Met de regel For ii = BronTabel(i, 2) To BronTabel(i, 3) verwijs je dus inmiddels naar de Array en niet meer naar het bereik op de sheet. De Array is in élk geval 3 kolommen breed. Vandaar dat ik daar hard kan verwijzen naar de tweede en derde kolom van die Array. De Array heeft dus niets meer te maken met welke positie in de sheet dan ook.
Kan je dit een beetje volgen zo?

 

[NogGeenGuru]

Beste Ginger en Alphamax,

bedankt voor jullie hulp en uitleg. ik begrijp nu wat de macro doet en ik heb mijn bestandje werkend gekregen.
zonder jullie had ik het niet gekund!

ik sluit nu het topic, mijn vraag is opgelost