PDA

View Full Version : Array navigation



knesten
02-01-2010, 11:45 PM
Hi.
I have two columns of data where I want to calculate a smoothing average for both. I'd like to solve it using arrays. First i fetch the first three numbers in column B and do the calculation which works fine.
The problem is, how do i get to column C and do the same?

I'm trying to use OFFSET but I get a compile error. Ideally I should read all the data from column B and C in my array and then do the calculations. This, however, gives me another problem. How do I tell the SUM function in line 12 to first calculate the average for B1:B3 and then for B2:B5 down to C10:C12? Writing ...SUM(dataind(i:i+2,0) gives me another error.


Option Explicit
Sub read()

Dim i As Integer, j As Integer
Dim output(3 To 12, 0)
Dim dataind() As Variant

For j = 1 To 2

For i = 1 To 10
dataind = Range("b" & i & ":B" & i + 2).Value
output(i + 2, 0) = Application.WorksheetFunction.Sum(dataind) / 3

Next i
dataind = dataind.Offset(j, 0) 'COMPILE ERROR: INVALID QUALIFIER
Next j

Range("d3:d12") = output

End Sub

RolfJ
02-02-2010, 01:10 AM
When you assign a range to a variant the variant will be a 2-dimensional array with the first dimension representing the row index and the second dimension the column index.

In order to store the range B1:B12 in the array dataind you would simply use this expression:


dataind = Range("B1:B12) 'without the i loop


The value of cell B1 is stored in dataind(1,1).

Likewise, if you were to declare


dataind = Range("B1:C12)


the value of cell C12 would be stored in dataind(12,2).

I am not entirely clear on what you are trying to do with the statement dataind = dataind.Offset(j, 0), but it is clear why it creates a compiler error: there is no Offset property for the variable dataind!

knesten
02-02-2010, 01:24 AM
Hi. I see what you mean, and I don't need the OFFSET statement if I read all the data into DATAIND. But how do i choose only three observations at a time in the FOR loop then?

output(i + 2, 0) = Application.WorksheetFunction.Sum(dataind) / 3

It doens't work when I write ...dataind(i:i+2,0)

So how do I work navigate in the array inside the loop?

Bob Phillips
02-02-2010, 01:28 AM
There is bound to be away to do what youy want, but it would help if you explained what it is that you want to do, you code is very confuisng.

knesten
02-02-2010, 01:36 AM
Alright. I've removed one of the loops and the OFFSET statement.
In the loop, in the first iteration, I want to calculate the average of B1:B3. For the second iteration I want to calculate the average for B2:B5, the third for B3:B6 and so on down to C10:C12.
I know how to store the result in my OUTPUT variable!
I don't know how to only choose B1:B3, and so on, inside the loop in the sumfunction.


Option Explicit

Sub read()

Dim i As Integer, j As Integer
Dim output(3 To 12, 0)
Dim dataind() As Variant

dataind = Range("b2:c12").Value
For i = 1 To 10

output(i + 2, 0) = Application.WorksheetFunction.Sum(dataind) / 3

Next i


Range("d3:e12") = output

End Sub

Bob Phillips
02-02-2010, 02:13 AM
Sub read()

Dim i As Integer, j As Integer
Dim Output(1 To 10, 1 To 2)

For i = 1 To 10

Output(i, 1) = Application.Sum(Cells(i, "B").Resize(3)) / 3
Output(i, 2) = Application.Sum(Cells(i, "C").Resize(3)) / 3
Next i
Range("D3:e12") = Output

End Sub

knesten
02-02-2010, 02:16 AM
Yeah that would of course be the easy solution. But what if i had 20 columns i wanted to do the calculation for. There must be some way to make the code run more dynamic than that.

Bob Phillips
02-02-2010, 02:27 AM
Sub read()

Dim i As Integer, j As Integer
Dim Output(1 To 10, 1 To 2)

For i = 1 To 10

For j = 1 To 2

Output(i, j) = Application.Sum(Cells(i, j + 1).Resize(3)) / 3
Next j
Next i

Range("D3:D12").Resize(, j - 1) = Output

End Sub

ZVI
02-02-2010, 07:37 AM
Another way:


Sub MovingAverage()
With Range("D3:E12")
.Formula = "=AVERAGE(B1:B3)"
.Value = .Value
End With
End Sub

RolfJ
02-02-2010, 09:15 AM
Hi knesten:

it seems that xld helped you pretty far along, but I thought it might help for future work if I was to address your question about how to address arrays


It doens't work when I write ...dataind(i:i+2,0)

So how do I work navigate in the array inside the loop?

I will not claim that this is the most expedient way to do this, but for the sake of this demonstration, one way to calculate the moving average would be as follows (it will place the moving average of column B in column D and that of column C in column E):


Sub MovingAverage()
Dim dataind As Variant
dataind = Range("B1:C12")
Dim i As Integer, j As Integer
For i = 3 To UBound(dataind, 1)
Dim avgColB As Double, avgColC As Double
avgColB = 0: avgColC = 0
For j = 0 To 2
avgColB = avgColB + dataind(i - 2 + j, 1)
avgColC = avgColC + dataind(i - 2 + j, 2)
Next j
avgColB = avgColB / 3
Range("D3").Offset(i - 3).Value = avgColB
avgColC = avgColC / 3
Range("E3").Offset(i - 3).Value = avgColC
Next i
End Sub