range to array

[arielon]

hi
I am writing a function that recieves to lists as range and I need to copy them into arrays. somehow I cant find a way to define the size of the arrays. because vb wont let me use application.count(list1) as a parameter.

I am attaching part of the function

Function attest(list1 As Range, list2 As Range)

avg1 = Application.Average(list1)
avg2 = Application.Average(list2)
n1 = Application.Count(list1)
n2 = Application.Count(list2)


Dim arr1(1 To n1) As Long
Dim arr2(1 To n2) As Long

For i = 1 To Application.Count(list1)
arr1(i) = 1
Next i

For i = 1 To Application.Count(list2)
arr2(i) = (list2(i) - avg2) ^ 2
Next i
end function

the error message is that the size of the aeeay must be constant. when trying to determing in Dynamicly I encounter the same problem on a loop that assigns the values

can anyone help?

[Kenneth Hobs]

Your range sizes passed will make a difference.

You are not returning a value. I added one to test.
Sub Test_attest()
Dim r1 As Range, r2 As Range, c As Range
Dim a

Set r1 = Range("A1:A10")
Set r2 = Range("B1:B10")
r1.Value = 5
For Each c In r2
c.Value = Evaluate("Row(" & c.Address & ")")
Next c

a = attest(r1, r2)
MsgBox Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(a)), vbCrLf)
End Sub

Function attest(list1 As Range, list2 As Range)
Dim avg1 As Double, avg2 As Double
Dim n1 As Long, n2 As Long
Dim arr1() As Double, arr2() As Double
Dim i As Long

avg1 = Application.Average(list1)
avg2 = Application.Average(list2)
n1 = Application.Count(list1)
n2 = Application.Count(list2)

ReDim arr1(1 To n1) As Double
ReDim arr2(1 To n2) As Double

For i = 1 To Application.Count(list1)
arr1(i) = 1
Next i

For i = 1 To Application.Count(list2)
arr2(i) = (list2(i) - avg2) ^ 2
Next i

attest = arr2()
End Function