Hoe Arrays instellen in Excel

[Plotinus]

Ik wil voor een heleboel rijen op een bepaald tabblad de gegevens kopiëren naar twee (wegings)kolommen en vervolgens moeten de daar berekende resultaten weer worden weggeschreven naar een ander tabblad. Ik doe dat nu als volgt:

For k = 1 To 5000
            For l = 1 To 33
                Worksheets("Actuele_Score").Range("M9").Offset(l, 0).Value = Worksheets("Resultaten").Range("K" & k).Offset(0, l - 1)
                Worksheets("Actuele_Score").Range("N9").Offset(l, 0).Value = Worksheets("Resultaten").Range("K" & k).Offset(0, l + 41)
            Next l
            Opslaan_data (aanroepen wegschrijf-routine)
        Next k

Dit werkt, maar duurt lang en moet naar mijn gevoel slimmer en sneller kunnen. Ik dacht het te doen met een Array; alle 33 items in één variabele inlezen en liefst ook in één slag weer uitlezen. Ik heb een eerste beginnetje en dat ziet er als volgt uit:

 With Worksheets("Resultaten")
        For k = 1 To 5000
            Data = Array(.Range("K" & k), .Range("L" & k), .Range("M" & k))
       Next k
    End With

Dat oogt ook niet handig: de array vullen is 33 keer '. Range(...)'; wordt dus een heel lange regel. Vraag is nu kan dit handiger? Alle in te lezen items liggen naast elkaar en aansluitend.
Vraag is ook hoe ik ze in één slag weer uitlees in het tabblad "Actuele score" zoals hierboven aangegeven:

Worksheets("Actuele_Score").Range("M9").Offset(l, 0).Value = Worksheets("Resultaten").Range("K" & k).Offset(0, l - 1)

 

[edmoor]

Je kan op deze manier alles ineens in een array stoppen:

    Dim aRng As Range
    Dim aTxt() As Variant
    
    Set aRng = Range("K1:M5000")
    aTxt = aRng

Je hebt dan aTxt(1,1) t/m aTxt(5000,3) tot je beschikking.

Ook kan je dan bijvoorbeeld dit doen:
Range("A6:C5006") = aTxt

Waarmee je dus de hele array van 15000 elementen ineens plaatst.

 

[snb]

Omdat het je om de waarden gaat heb je alleen maar de waarde-eigenschappen van het gebied nodig; die lees je in een keer in

Sub M_snb()
   sn=sheets(1).Range("K1:M5000")
end sub

In deze array ( sn) kun je alle berekeningen uitvoeren.
De resultaten schrijf je in 1 keer weg met:

Sub M_snb()
   sheets(2).range("A1:B5000")=sn
end sub

 

[Plotinus]

bedankt edmoor en snb. Ik heb toch nog een paar aanvullende vragen:

Het spoor van edmoor volgend lukt het me wel het gehele datablok in één keer in te lezen en één voor één uit te lezen, maar niet rij voor rij. Ik heb het nu als volgt gemaakt:

 Dim Importdata As Range
 Dim aText() As Variant
 Set Importdata = Worksheets("Resultaten").Range("K1: AQ5000")
 aText = Importdata
    With Worksheets("Actuele_score")
        For k = 1 To 5000
         [I]   Range("M10: M33") = aText(k, 1 : k,33)[/I]
        Next k
    End With

Het stukje wat cursief staat, daar gaat het fout: ik wil in de range van M10-M33 rij voor rij inlezen en wegschrijven. Als verduidelijking heb ik het probleem heel eenvoudig voorgesteld in bijgesloten bestand: in het tabblad 'resultaten' zijn de meetresultaten weergegeven, die moeten rij voor rij door het tabblad 'weging' worden gejaagd en vervolgens weer op een ander tabblad worden weggeschreven. Als je me daar nog mee zou willen helpen, heel graag.

Wat snb voorsteld is iets waar ik ook aan gedacht heb, als iets wat ook zou moeten kunnen, maar daar weet ik echt niet hoe aan te beginnen, maar ongetwijfeld zal het nog meer snelheidswinst opleveren. Als je me een suggestie wilt doen, heel graag.

Het gebruik van een apart wegingstabblad heeft dan weer het voordeel dat het door een niet onderlegde gebruiker makkelijk is aan te passen, dus niet in de code moet duiden.

 

[snb]

Hoe je aan het berekende resultaat komt heb je nagelaten te vermelden.
In het werkblad weging heb ik de lege kolommen (vermijd die immer !) C en F eerst verwijderd.

Sub M_snb()
   sn = Sheets("resultaten").Cells(1).CurrentRegion
   sp = Sheets("weging").Cells(1).CurrentRegion
   
   For j = 1 To UBound(sn)
     For jj = 1 To UBound(sn, 2)
       sn(j, jj) = IIf(sp(jj + 2, 3) = 1, sp(jj + 2, 2) * sn(j, jj), "")
     Next
   Next
   
   Sheets("gewogen resultaten").Cells(10, 1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

Meer over het gebruik van Arrays: http://www.snb-vba.eu/VBA_Arrays.html

 

[Plotinus]

bedankt snb, het is inderdaad ongelooflijk snel nu - schitterend! Alleen de bewerking is nog niet helemaal juist en heb ik nog niet door hoe ik die juist kan krijgen.

Het gaat om twee reeksen datasets die naast elkaar in rijen in tabblad 'resultaten' zijn weergegeven, van kolom A tm AD en van AG tot BJ. Deze reeksen moeten met elkaar worden vermenigvuldig (zie kolom C en D in tabblad 'weging') en met een wegingsfactor (zie wegingskolom, tabblad 'weging').Als voorbeeld heb ik de eerste rij van de datasets 1 en 2 handmatig in het tabblad 'weging' ingelezen met het gewenste resultaat in kolom E, zie bijlage.
Alleen de resultaten na de weging moeten in de uitvoer komen. Ik zit er dus ook mee hoe ik bij de uitvoer de eerst rij (Datablok 1 en Datablok 2) niet afdruk in het tabblad 'gewogen resultaten'.

Zou je nog een willen kijken?

 

[snb]

Bestanden met samengevoegde cellen bekijk ik niet.
Nergens staat aangegeven hoe je de berekening maakt.

 

[Plotinus]

Ik heb de cel-samenvoeging verwijderd; is dit een gevaarlijke actie/functie?

Het gaat in onderhavige probleem om een vermenigvuldiging van drie kolommen: kolom B, C en D op tabblad 'weging'. In de kolommen C en D worden de twee datablokken van iedere rij (2 *30 meetresultaten) van tabblad 'resultaten' ingelezen, dus onderling en met kolom B vermenigvuldigd , zie tabblad 'weging' met het gewenste resultaat in kolom E voor rij één. Dit moet voor iedere rij uitgevoerd worden, waarbij rij voor rij het resultaat van kolom E weer wordt weggeschreven in tabblad 'gewogen resultaten'.

Ik vrees dat ik het niet duidelijker kan uitleggen; mogelijk begrijp ik je verkeerd(?)