I am trying create a VB function that will take two ranges of cells as inputs
(both in columns) from Excel, convert them to arrays, perform necessary
calculations on them within a third array, and output the computed array as a
range of cells (in column form) back to Excel. As is, I am receiving a #REF!
error. I want the function to return the results starting from the same cell
that the function is called upon. Thus, if I go into cell "A1" to call my
function, I want the results to appear as a column on A1 and the cells below
it. Below is my function. Any quick tips you could give would be great.

Thanks.


Function RCNReduction(ByVal Prange As Range, ByVal TRTrange As Range) As
Double()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim n As Variant
Dim P_CIN() As Variant
Dim CIN_Outcome() As String
Dim percentbracket() As Double
Dim CountYes() As Integer
Dim CountNo() As Integer
Dim Difference() As Double

P_CIN = Cells.Value(Prange)
CIN_Outcome = Cells.Value(TRTrange)

For j = 1 To 1000
percentbracket(j) = j * (1 / 1000)
Next j

For i = 1 To UBound(P_CIN)

For k = 1 To 1000
If P_CIN(i, 1) = percentbracket(k) And P_CIN(i, 1) < (percentbracket(k)
+ 0.001) Then
If CIN_Outcome(i, 1) = YES Then
CountYes(k) = CountYes(k) + 1
End If
If CIN_Outcome(i, 1) = NO Then
CountNo(k) = CountNo(k) + 1
End If
End If

Next k
Next i

n = 0

For l = 1 To 1000
If CountNo(l) 0 Or CountYes(l) 0 Then
n = n + 1
Difference(n, 1) = ((percentbracket(l) + 0.0005)) - CountYes(l) /
(CountNo(l) + CountYes(l))
End If
Next l

RCNReduction = Difference

Exit Function

End Function

There may be more errors, but as a minimum

Function RCNReduction(ByVal Prange As Range, _
ByVal TRTrange As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim n As Variant
Dim P_CIN As Variant
Dim CIN_Outcome As Variant
Dim percentbracket() As Double
Dim CountYes() As Integer
Dim CountNo() As Integer
Dim Difference() As Double

P_CIN = Prange.Value
CIN_Outcome = TRTrange.Value

For j = 1 To 1000
percentbracket(j) = j * (1 / 1000)
Next j

For i = 1 To UBound(P_CIN)

For k = 1 To 1000
If P_CIN(i, 1) = percentbracket(k) And _
P_CIN(i, 1) < (percentbracket(k) + 0.001) Then
If Ucase(CIN_Outcome(i, 1)) = "YES" Then
CountYes(k) = CountYes(k) + 1
ElseIf Ucase(CIN_Outcome(i, 1)) = "NO" Then
CountNo(k) = CountNo(k) + 1
End If
End If
Next k
Next i

n = 0

For l = 1 To 1000
If CountNo(l) 0 Or CountYes(l) 0 Then
n = n + 1
Difference(n, 1) = ((percentbracket(l) + _
0.0005)) - CountYes(l) / _
(CountNo(l) + CountYes(l))
End If
Next l

RCNReduction = Difference

End Function


--
Regards,
Tom Ogilvy

"Neema" wrote:

I am trying create a VB function that will take two ranges of cells as inputs
(both in columns) from Excel, convert them to arrays, perform necessary
calculations on them within a third array, and output the computed array as a
range of cells (in column form) back to Excel. As is, I am receiving a #REF!
error. I want the function to return the results starting from the same cell
that the function is called upon. Thus, if I go into cell "A1" to call my
function, I want the results to appear as a column on A1 and the cells below
it. Below is my function. Any quick tips you could give would be great.

Thanks.


Function RCNReduction(ByVal Prange As Range, ByVal TRTrange As Range) As
Double()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim n As Variant
Dim P_CIN() As Variant
Dim CIN_Outcome() As String
Dim percentbracket() As Double
Dim CountYes() As Integer
Dim CountNo() As Integer
Dim Difference() As Double

P_CIN = Cells.Value(Prange)
CIN_Outcome = Cells.Value(TRTrange)

For j = 1 To 1000
percentbracket(j) = j * (1 / 1000)
Next j

For i = 1 To UBound(P_CIN)

For k = 1 To 1000
If P_CIN(i, 1) = percentbracket(k) And P_CIN(i, 1) < (percentbracket(k)
+ 0.001) Then
If CIN_Outcome(i, 1) = YES Then
CountYes(k) = CountYes(k) + 1
End If
If CIN_Outcome(i, 1) = NO Then
CountNo(k) = CountNo(k) + 1
End If
End If

Next k
Next i

n = 0

For l = 1 To 1000
If CountNo(l) 0 Or CountYes(l) 0 Then
n = n + 1
Difference(n, 1) = ((percentbracket(l) + 0.0005)) - CountYes(l) /
(CountNo(l) + CountYes(l))
End If
Next l

RCNReduction = Difference

Exit Function

End Function