Help!

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

Hulp bij posten

Recente topics

Auteur Topic: For... Next.... vraagje  (gelezen 1837 keer)

0 leden en 1 gast bekijken dit topic.

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
For... Next.... vraagje
« Gepost op: 13 mei 2021, 07:41:20 »
Hallo allen,

Ik heb een vraagje omtrent de For.... Next... loop van VBA.

Is het mogelijk om de resultaten uit zulke loop tijdelijk in het geheugen van VBA op te slaan om er naderhand, buiten de loop, eerst onderling berekeningen/vergelijkingen mee te kunnen doen alvorens er een eindresultaat wordt weggeschreven in het doel-werkblad?
Zoja, op welke wijze dan?

Alvast dank op voorhand.

Groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Online JEC

  • Excel-Expert
  • Ervaren lid
  • *****
  • Berichten: 422
Re: For... Next.... vraagje
« Reactie #1 Gepost op: 13 mei 2021, 09:26:04 »
Dat kan door te werken met arrays.

Vul de array op basis van voorwaarden en schrijf die uiteindelijk in ene weg. Dat is ook vele malen sneller dan een voor een wegschrijven.

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: For... Next.... vraagje
« Reactie #2 Gepost op: 13 mei 2021, 09:55:03 »
Hey veerj,

Hartelijk dank voor je respons en de info.
Alweer, door mijn beperkte kennis heb ik zelf nog nooit met arrays gewerkt...

Hoe zou ik dit voor volgende (geneste) For... Next... loop dan best kunnen aanpakken?
Sub verwerk()
    With Sheets("data")
        gezinnen = .Cells(Rows.Count, 1).End(xlUp).Row
        For s = 4 To gezinnen
        If (Sheets("verwerk").Cells(4, 2) = .Cells(s, 1) And Sheets("verwerk").Cells(3, 2) = .Cells(s, 2)) Or (Sheets("verwerk").Cells(5, 2) = .Cells(s, 1) And .Cells(s, 2) = "") Then
            rang = .Cells(s, 3)
            kgdag = .Cells(s, 6)
            kgmnd = .Cells(s, 7)
            kgjaar = .Cells(s, 8)
                With Sheets("verwerk")
                    For rij = 1 To 15
                        If rij = rang Then
                        'ipv weg te schrijven zou dit resultaat dus tijdelijk opgeslagen moeten worden
                        .Cells(rij, 10) = kgmnd + (kgjaar * 12)
                        End If
                    Next rij
                End With
        End If
        Next s
    End With
End Sub

Alvast dank op voorhand,
groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Online JEC

  • Excel-Expert
  • Ervaren lid
  • *****
  • Berichten: 422
Re: For... Next.... vraagje
« Reactie #3 Gepost op: 13 mei 2021, 10:26:00 »
Ik zou ook even je bestand meesturen met daarin de gewenste uitkomst.

Misschien dat je hier al mee uit de voeten kan

Sub verwerk()
    With Sheets("data")
      ReDim ar(20)      'aanmaken van array (wat ik overigens iets anders zou doen als ik inzicht had in het bestand)
        For s = 4 To .Cells(Rows.Count, 1).End(xlUp).Row
          For rij = 1 To 15
            If (Sheets("verwerk").Cells(4, 2) = .Cells(s, 1) And Sheets("verwerk").Cells(3, 2) = .Cells(s, 2)) Or (Sheets("verwerk").Cells(5, 2) = .Cells(s, 1) And .Cells(s, 2) = "") Then
              rang = .Cells(s, 3)
              kgdag = .Cells(s, 6)
              kgmnd = .Cells(s, 7)
              kgjaar = .Cells(s, 8)
              If rij = rang Then
                 ar(j) = kgmnd + (kgjaar * 12)    'hier wordt alles opgeslagen in een array
                 j = j + 1
              End If
           End If
          Next rij
        Next s
      Sheets("verwerk").Cells(1, 10).Resize(j) = Application.Transpose(ar)   'alles in ene wegschrijven
    End With
End Sub

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: For... Next.... vraagje
« Reactie #4 Gepost op: 13 mei 2021, 10:55:20 »
Hey veerj,

Mijn excuses.
Bovenstaande code-blokje staat eigenlijk binnen een groter codeblok maar voor mijn huidige vraag was het vooral dit code-blokje dat er toe deed omdat het enkel hiermee was dat ik volledig vast zat. Dus stuurde ik enkel dit code-blokje door.
Had er niet onmiddellijk bij stilgestaan dat je ook moet weten wat er uiteindelijk met de 'opgeslagen' resultaten moet gebeuren en waar dan de eindresultaten na de nodige berekeningen en vergelijkingen moeten worden weggeschreven.

Het bestand heeft twee werkbladen : "data" en "verwerk".
"data" is gewoon de gegevenstabel.
"verwerk" is dan het blad waar ik de eindresultaten wil krijgen.
Met de rest van de code heb ik het reeds voor elkaar gekregen om één gedeelte te laten uitvoeren met enkel de eindresultaten.
Voor de duidelijkheid heb ik op "verwerk" enkele kleuraanduidingen en uitleg gegeven.
de groen gekleurde cel is de zoek-waarde die ik handmatig invul.
de roze cellen bevatten formules.
de blauwe cellen zijn dan voor de eindresultaten van de berekeningen die door VBA ingevuld worden.
Het grijze gedeelte is dan enerzijds de 'tijdelijke' waarden uit de binnenste loop uit bovenstaand code-blok met daarnaast de uitleg welke berekeningen/vergelijkingen er dienen te gebeuren.
Uiteindelijk is het de bedoeling dat de waarden in het grijze gedeelte niet meer worden weergegeven.

Hopelijk raak je wat wijs uit mijn chaotische uitleg  ;D

groetjes,
BlackDevil


1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Online JEC

  • Excel-Expert
  • Ervaren lid
  • *****
  • Berichten: 422
Re: For... Next.... vraagje
« Reactie #5 Gepost op: 13 mei 2021, 11:13:59 »
Ik kan je nog wel een random voorbeeldje geven over hoe je een array vult en wegschrijft op basis van jouw data.
Hoe je het vervolgens in jouw code toepast is aan jou:) Het is te veel om even snel aan te passen.

Run onderstaande code maar eens. Met wat uitleg ernaast kun je er voor jezelf wat beter uitkomen denkik!

Sub jvr()
 jv = Sheets("data").Cells(3, 1).CurrentRegion                          'je data inlezen
  ReDim ar(UBound(jv))                                                  'array groot genoeg nemen door bovengrens van data te pakken
     For i = 2 To UBound(jv)                                            'loop door data
        If jv(i, 13) <> "" Then                                         'Alleen meenemen als Huw-plaats niet leeg is (als voorbeeld)
          ar(j) = jv(i, 1)                                              'Sosa nmr in array zetten
          j = j + 1                                                     'eentje ophogen voor de volgende waarde
        End If
      Next
    Sheets("data").Cells(4, 27).Resize(j) = Application.Transpose(ar)   'in ene wegschrijven in kolom AA
End Sub

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: For... Next.... vraagje
« Reactie #6 Gepost op: 13 mei 2021, 13:04:17 »
Hey veerj,

Hartelijk dank voor je bijdrage.
Na wat bestuderen en uitproberen op mijn data begint het te lukken de array aan te maken.
MAAR heb nu een ander probleempje.
Met mijn oorspronkelijk code-blok kreeg ik de 'tijdelijke' resultaten in oplopende volgorde volgens het rangnummer.
Nu met de code voor de aanmaak van de array komen de waarden in de volgorde dat ze voorkomen in de gegevenstabel en dus niet volgens het rangnummer. De juiste volgorde is namelijk cruciaal voor de berekeningen en vergelijkingen die ik op de array-elementen moet uitvoeren alvorens de eindresultaten (terug naar datumformaat) weg te schrijven.
Ben al eventjes aan het zoeken op internet hoe je een array kan sorteren maar vind niet meteen een simpele manier.
Is het misschien mogelijk om de array van in het begin in de juiste volgorde te laten vullen?

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Online JEC

  • Excel-Expert
  • Ervaren lid
  • *****
  • Berichten: 422
Re: For... Next.... vraagje
« Reactie #7 Gepost op: 14 mei 2021, 12:01:16 »
Moet er dan gesorteerd worden van hoog naar laag? Of andersom

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: For... Next.... vraagje
« Reactie #8 Gepost op: 14 mei 2021, 12:21:08 »
Hey Veerj,

Van laag naar hoog...
Het rangnummer is de volgorde waarin de kinderen geboren zijn, dus het eerste kind moet eerst komen, enz..

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 2.277
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: For... Next.... vraagje
« Reactie #9 Gepost op: 14 mei 2021, 12:46:27 »
BlackDevil, misschien leuk om een Array beter te begrijpen. Een Worksheet IS een Array. ;)
Vandaar dat je de data vanuit een bereik in een Worksheet zomaar in een Array-variabele kan zetten. Helaas heeft de Array in VBA dan dus niet de eigenschappen van de Range uit de Worksheet. Maar in principe zou je wel met er doorheen lussen, dezelfde zaken moeten kunnen regelen. Sorteren van een Array is inderdaad een lastig proces. Daar zijn een hoop voorbeelden van te vinden via Google. Wat ik zelf vaak doe als iets moet worden gesorteerd, is de data éérst even wegschrijven naar bijvoorbeeld een verborgen sheet. Daar dus op de sheet je sortering uitvoeren en dan die gesorteerde data inlezen in een VBA-Array.
Maar goed, ik weet nu ff niet of de sortering in je huidige codeblokje wordt geregeld met iets met zoeken ofzo. Dat kan ook in een Array, maar ben je wel weer een stapje verder met gevorderde te worden.
______________________________

Groet, Leo

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: For... Next.... vraagje
« Reactie #10 Gepost op: 14 mei 2021, 13:13:40 »
@RedHead

Hartelijk dank voor je uitgebreide toelichting.
Onderstaand is de door mij aangepaste code (die Veerj me had bezorgd om een array te vullen) die ik tot hiertoe heb.

Sub jvr()
 jv = Sheets("data").Cells(3, 1).CurrentRegion
  ReDim ar(UBound(jv))
     For s = 2 To UBound(jv)
        If (Sheets("verwerk").Cells(4, 2) = jv(s, 1) And Sheets("verwerk").Cells(3, 2) = jv(s, 2)) Or (Sheets("verwerk").Cells(5, 2) = jv(s, 1) And jv(s, 2) = "") Then
        rang = jv(s, 3)
        kgdag = jv(s, 6)
        kgmnd = jv(s, 7)
        kgjaar = jv(s, 8)
        ar(j) = kgmnd + (kgjaar * 12)
          j = j + 1
        End If
      Next
      'Hier dienen eerst berekeningen/vergelijkingen te gebeuren tussen de verschillende elementen
      'maar ook met zoekresultaten uit andere loops alvorens er iets definitiefs mag 'weggeschreven' worden
     
'    Sheets("data").Cells(4, 27).Resize(j) = Application.Transpose(ar)
End Sub

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Online JEC

  • Excel-Expert
  • Ervaren lid
  • *****
  • Berichten: 422
Re: For... Next.... vraagje
« Reactie #11 Gepost op: 14 mei 2021, 17:34:30 »
Zo kun je een array inlezen, sorteren en wegschrijven. Run maar weer eens in je laatste voorbeeldbestand.

Sub jvvrr()
   jv = Sheets("data").Cells(3, 1).CurrentRegion
   With CreateObject("System.Collections.Arraylist")
      For i = 2 To UBound(jv)
        If jv(i, 13) <> "" Then .Add jv(i, 1)
      Next
     .Sort
      Sheets("data").Cells(4, 27).Resize(.Count) = Application.Transpose(.toarray)
   End With
End Sub

Of met een andere methode, door te sorteren binnen je eigen aangemaakte array

Sub jverr()
 jv = Sheets("data").Cells(3, 1).CurrentRegion
  ReDim ar(UBound(jv) + 1)
     For i = 2 To UBound(jv)
        If jv(i, 13) <> "" Then ar(j) = jv(i, 1): j = j + 1
     Next
     For i = 0 To j
       For ii = i + 1 To j
         If ar(ii) < ar(i) And ar(i) <> "" And ar(ii) <> "" Then
            a = ar(i)
            ar(i) = ar(ii)
            ar(ii) = a
          End If
        Next
      Next
    Sheets("data").Cells(4, 27).Resize(j) = Application.Transpose(ar)
End Sub


Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: For... Next.... vraagje
« Reactie #12 Gepost op: 15 mei 2021, 16:39:56 »
@Veerj,

Hartelijk dank voor de extra bijdrage.
Ik zal dit vant weekend eens grondig bestuderen en laat nog weten of ik het al dan niet 'werkend' krijg.

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

 


www.combell.com