Looping through a column, selecting range after range

[emdee]

Ok.. what I'm trying to do:
My observations, around 4000 per column, are on sheet 1. Starting with the first column, I want to calculate the population variance (VARP) of each set of 21 observations (range of +20 rows down), put the result on sheet 2 and then do the same for the next 21, put the result below the first result on sheet 2 and so on until the observations run out. This should be done for all the columns.

I tried building the inner loop for calculating VARP but I'm not sure how to increment and select the range of 21 rows to feed to VARP. Any help here would be highly appreciated!

 

[Andrew Poulsom]

Could you post your current code?

 

[emdee]

Here's my (obviously) non-working code so far. Hope you get from here what I'm trying to accomplish. I have Dimmed those variables as ??? cause I don't know the right type.

'Dim rangestart As ???
'Dim rangeend As ???
Dim rangetocalc As Range

rangestart = Worksheets("dr").Range("B8").Address
rangeend = rangestart + rangestart.Offset(20, 0)
Worksheets("variances").Range("B1").Activate

For i = 0 To 200
rangetocalc = Range("rangestart:rangeend")
Worksheets("variances").ActiveCell.Offset(i, 0).Value = Application.WorksheetFunction.VarP(rangetocalc)
rangestart = rangeend + rangeend.Offset(1, 0)
rangeend = rangestart + rangestart.Offset(20, 0)
Next i

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh1 As Worksheet
    Dim Rng1 As Range
    Dim Arr1() As Variant
    Dim Sh2 As Worksheet
    Dim i As Long
    Dim r As Long
    Dim c As Integer
    Dim j As Long
    Dim Arr2() As Variant
    Set Sh1 = Worksheets("Sheet1")
    Set Rng1 = Sh1.Range("A1").CurrentRegion
    ReDim Preserve Arr1(1 To Rng1.Cells.Count)
    Set Sh2 = Worksheets("Sheet2")
    Sh2.Cells.ClearContents
    i = 0
    For c = 1 To Rng1.Columns.Count
        For r = 1 To Rng1.Rows.Count
            i = i + 1
            Arr1(i) = Rng1.Cells(r, c).Value
        Next r
    Next c
    ReDim Preserve Arr2(1 To 21)
    r = 1
    For i = 1 To UBound(Arr1) Step UBound(Arr2)
        For j = 1 To UBound(Arr2)
            Arr2(j) = Arr1(i + j - 1)
        Next j
        Sh2.Cells(r, 1) = WorksheetFunction.VarP(Arr2)
        r = r + 1
    Next i
End Sub

 

[emdee]

Thanks a lot for your help!! I modified it a bit and now it does exactly what I wanted:

Sub Test2()
    Dim Sh1 As Worksheet
    Dim Rng1 As Range
    Dim Arr1() As Variant
    Dim Sh2 As Worksheet
    Dim i As Long
    Dim r As Long
    Dim c As Integer
    Dim j As Long
    Dim Arr2() As Variant
    Set Sh1 = Worksheets("Sheet1")
    Set Rng1 = Sh1.Range("A1").CurrentRegion
    ReDim Preserve Arr1(1 To Rng1.Rows.Count)
    Set Sh2 = Worksheets("Sheet2")
    Sh2.Cells.ClearContents
    
   
    For c = 1 To Rng1.Columns.Count
    i = 0
        For r = 1 To Rng1.Rows.Count
            i = i + 1
            Arr1(i) = Rng1.Cells(r, c).Value
        Next r
    ReDim Preserve Arr2(1 To 21)
       
        r = 1
        For i = 1 To UBound(Arr1) Step UBound(Arr2)
            For j = 1 To UBound(Arr2)
              Arr2(j) = Arr1(i + j - 1)
         Next j
            Sh2.Cells(r, c) = Application.VarP(Arr2)
            r = r + 1
        Next i
    
    Next c

End Sub