I have written the following in a module.

Private Function Test(j As Integer) As Single
Dim Check(20) As Variant
Dim ULTemp(20) As Single
Dim LLTemp(20) As Single
Dim UL(20) As Single
Dim LL(20) As Single
Dim i, k As Integer
k = 4
i = 0
j = 0
Do Until i = 20
Check(i) = Worksheets("Sheet1").Cells(k, 3)
ULTemp(i) = Worksheets("Sheet1").Cells(k, 4)
LLTemp(i) = Worksheets("Sheet1").Cells(k, 5)

If Check(i) < "" Then
UL(j) = ULTemp(i)
LL(j) = LLTemp(i)
j = j + 1
End If

i = i + 1
k = k + 1

Loop
Test = UL(j)
End Function

Sub DropDown4_Change()
If Range("F13") = 1 Then
Application.ScreenUpdating = False
Cells(1, 1) = Test(1)

Application.ScreenUpdating = True
End If
End Sub

the user get to select one of many options from the dropdown form on
sheet 2.

once the user select one of the options it will then triger the Test
function to scan a few columns from sheet 1 and omit the empty rows and
only keep the rows with # into an array.

I want to be able to access the saved data in the array and do some
calculation on it.

But when ever option 1 is chosen it returned a value of 0 everytime.

any suggestion to what I might be doing wrong?

Thanks.

Hi

your function doesn't make sense

Private Function Test(j As Integer) As Single

but you initialize the passed variable by

j=0

what do you want to do

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=52882

I finally found the problem with my function. it's working good nice.

thanks