variabel berijk naar array

[Joren88]

Weet er iemand hoe ik een variabel celbereik in een array-variabele kan stoppen?

Ik zou een bestand willen maken voor gegevensanalyse.
Maar de hoeveelheid gegevens is telkens anders, de ene keer cellen C1 tot C100 en de andere keer C1 tot C248 bijv.
Hoe krijg ik de waarden die in deze cellen staan in een array in VBA.

Hieronder de code die ik gebruik voor een vast gegevensbereik

Option Explicit
Private Machinenr() As Variant

Sub Machine()

Machinenr = Range("B2:B50").Value

  
End Sub

Ik heb het al geprobeerd met End(xlDown) maar dit werkt niet omdat deze code blijkbaar enkel bedoeld is om de onderste cel in een bereik te selecteren, dit geeft een range object terug.

 

[HSV]

bv:

range("B2:B"& cells(rows.count,2).end(xlup).row)

of als het een aaneengesloten bereik is.

Range(Range("B2"), Range("B2").End(xlDown))

Of een aaneengesloten bereik als voorwaarde dat B1 en de omliggende cellen leeg zijn.

Range("B2").CurrentRegion

 

[OctaFish]

Volgens mij bedoelt TS het andersom: hoe zet ik een variabele range in een matrix? Bijvoorbeeld zo:

Dim Arr() As Variant
Arr = Blad1.Range("C3:M8")

 

[paulva]

Misschien dit?

'Range("B2:B50").Select

Dim RijBegin As Integer
Dim RijEinde As Integer
Dim KolomBegin As Integer
Dim KolomEinde As Integer

RijBegin = 2
RijEinde = 50
KolomBegin = 2
KolomEinde = 2

Range(Cells(RijBegin, KolomBegin), Cells(RijEinde, KolomEinde)).Select

 

[Joren88]

Ik heb deze code gebruikt en nu lukt het om het in de array te krijgen en de array weer te even in een andere kolom.

Range(Range("B2"), Range("B2").End(xlDown))

Als ik echter de array wil doorlopen met een for-lus krijg ik steeds de fout: "Het subscript valt buiten het bereik"
Hieronder de gebruikte code.

Option Explicit
Private Machinenr() As Variant
Private Machinenr2() As Integer



Sub Machine()
Dim i As Integer

Machinenr = Range(Range("A2"), Range("A2").End(xlDown)).Value

For i = 0 To 50
Machinenr2(i) = Machinenr(i)
Next i

For i = 0 To UBound(Machinenr)
   If Machinenr2(i) = 0 Then
       Machinenr2(i) = Machinenr2(i - 1)
   End If
Next i


Range("H2:H200") = Machinenr2

  
End Sub

Het loopt reeds fout bij de eerste for lus

En ook nog extra vraagje, hoe krijg ik vba zover dat ook de range waar ik de array in wil plaatsen variabel is

 

[OctaFish]

Als je niet weet hoe groot de matrix is, en je hem dus dynamisch laat vullen, dan moet je niet met vaste waarden door de matrix proberen te lussen. Goede kans dat er dan buiten de dimensies wordt gewerkt. En waarom zou je, als je die dimensies heel makkelijk kunt uitvragen?
Dus niet:

     For i = 0 To 50

maar

     For i = LBound(Machinenr) To UBound(Machinenr)

Overigens moet je van Machinenr de dimensies ook nog instellen.

 

[Joren88]

Ik had heet eerst al met de Lbound en Ubound geprobeerd, maar dit werkte niet, daarom dat ik het eens met vaste waarden geprobeerd had.
Als ik de grenzen van mijn array vastleg, is hij dan nog dynamisch?

Ik ben zelf nog wat aan het experimenteren geweest met mijn code en kom tot volgende conclusies:
-Het probleem situeert zich in het feit dat ik een range object gebruikt om mijn dynamische array te vullen.
-Om één of andere reden kent deze array zijn eigen bounds niet, ik kan de bounds wel opvragen en in een cel plaatsen maar ik kan de bounds onmogelijk gebruik.
-In de verdere ontwikkeling van mijn programma kan het feit dat de array van het type variant is voor problemen zorgen als ik bewerkingen met de inhoud van de array wil gaan doen.

Kloppen deze conclusies?

Dan is mijn volgende vraag.
Is het mogelijk om een variabele set van gegevens (1 kolom) in een array van het type int te stoppen?
Moet ik dan achteraf deze array nog zijn Ubound meegeven of gaat hij deze dan zelf kennen, zo ja, hoe geef ik deze dan mee?
Ik heb het bij de variant array reeds geprobeerd met

Dim grootte as integer
grootte = UBound(Machinenr)
Redim Preserve Machinenr(grootte)

 

[HSV]

Zo werkt het, op één voorwaarde.
Als de eerste waarde (i = 1) in de array 0 is loopt het fout natuurlijk:

If Machinenr2(i) = 0 Then Machinenr2(i) = Machinenr2(i - 1)

(i-1) kan natuurlijk niet, dus daar moet je een ander oplossing voor verzinnen.

Private Machinenr As Variant

Sub Machine()
Dim i As Long
Machinenr = Range(Range("A2"), Range("A2").End(xlDown))
ReDim Machinenr2(LBound(Machinenr) To UBound(Machinenr))
    For i = LBound(Machinenr) To UBound(Machinenr)
      Machinenr2(i) = Machinenr(i, 1)
    Next i
      For i = 1 To UBound(Machinenr)
        If Machinenr2(i) = 0 Then Machinenr2(i) = Machinenr2(i - 1)
      Next i
  Range("H2").Resize(UBound(Machinenr)) = Application.Transpose(Machinenr2)
End Sub

En om de code eenvoudiger en sneller te maken doet onderstaande code hetzelfde,

Private Machinenr As Variant
Sub Machine()
Dim i As Long
Machinenr = Range(Range("A2"), Range("A2").End(xlDown))
    For i = LBound(Machinenr) To UBound(Machinenr)
      If Machinenr(i, 1) = 0 Then Machinenr(i, 1) = Machinenr(i - 1, 1)
    Next i
  Range("G2").Resize(UBound(Machinenr)) = Machinenr
End Sub

 

[Joren88]

de code werkt nu, maar ik heb toch nog 1 vraagje.

1 van de gegevens die ik inlees is 1878170, als ik mijn 2e array als integer definieer geeft hij een overflow fout als ik deze als long definieer niet. Normaal gezien is de waarde 1878170 toch perfect in een integer opslaan.

 

[HSV]

Een integer gaat tot 32768.

 

[Joren88]

inderdaad, foutje van mij.

Nog 1 vraagje.

1 van de kolommen bevat lege cellen, maar is wel steeds even lang als de kolom ernaast.
als ik dan de gewone code gebruik stopt hij telkens na de eerstvolgende waarde na de eerste set 0 gegevens.
Ik heb dit ook geprobeerd met volgende code

range("B2:B"& cells(rows.count,3).end(xldown).row)

, waarbij ik dan de rij ernaast laat tellen, maar deze geeft dan bij end(xldown) een waarde van -4121 aanterwijl ik maar 200 rijen data heb.

 

[HSV]

Dat is de code die bij 'end(xldown)' hoort.
Verander 'end(xldown)' in 'end(xlup)'.

 

[Joren88]

zo werkt het inderdaad wel.

Kan je mij uitleggen waarom ik hier end(xlUp) ipv end(xlDown) moet gebruiken, mijn redenatie was dat ik van cel B2 naar beneden tel

 

[HSV]

Dat heb ik in #2 van 5-1-2015 22:02 uur al uitgelegd.

 

[Joren88]

ik ben de post nog eens gaan nalezen, maar snap nog steeds niet goed waarop de ene keer xlup en de andere keer xldown.

 

[HSV]

Met "xldown" moet het een aaneengesloten bereik zijn, en telt van boven naar onderen.
vb: Van A1 tot laatste cel aaneengesloten met A1.

Met "xlUp" tel je de cellen vanaf onderen naar boven, en stopt bij de laatst ingevulde cel.
vb. Van A1 tot laatste cel vanaf onderen geteld.

 

[Joren88]

waar zit dan mijn redeneerfout als ik zeg dat als je van onder naar boven telt de laatst ingevuld cel A2 is?
Vanwaar begin je dan te tellen?
Het gaat hier om een aaneengesloten bereik en toch moet ik xlUp gebruiken om mijn code te laten werken...

 

[HSV]

In je post van 17:43 schreef je dat er lege cellen in zaten.

Als er helemaal niets is ingevuld in een kolom pakt het automatisch de eerste cel.

Test maar een wat met "Select" op kolom A.
Vul range("a1:a10") met gegevens.
bv.

range("a1", range("A1").end(xldown)).select

maak nu bv. cel A7 leeg.

Doe dat nu ook eens met.

range("A1:A" & cells(rows.count,1).end(xlup).row).select

En ook eens met:

Range("A1").currentregion.select