I have a UDF that goes like this:

Private Function GetValue(Account As String, DeptID As String, DayofPeriod
As Integer, Period As Integer, FiscalYear As Integer) As Single
Dim rcslarray
Dim tempholdingcell As Double
Dim lastrow As Integer

rcslarray = [RCSLData]

GetValue = 0
For lastrow = 2 To UBound(rcslarray, 1)
If rcslarray(lastrow, 2) = Period And rcslarray(lastrow, 3) = DeptID
And rcslarray(lastrow, 4) = Account And rcslarray(lastrow, 1) = FiscalYear
Then
If Mid(Account, 1, 1) = 3 Then
tempholdingcell = rcslarray(lastrow, DayofPeriod + 4) * -1
Else
tempholdingcell = rcslarray(lastrow, DayofPeriod + 4)
End If
End If
Next
GetValue = tempholdingcell
End Function

basically, its take a range passing it into an array, finding the values I
want from the parameters passed over by the funciton, and returning the value
I want.

problem is when it is returning a zero value, although the formating is
correct in the cells, it shows up as $0 rather than $- as it should be. I
tested the value to see if it returned a 0 value to find that there is some
"rounding" or lack of precision. is there anyway that i can get this done
within the function?

PS. I tried the rounding tempholdingcell but that didn't work and I have
tried to alter the data type of GETValue from double to single.

PSS. since the range is rather large and there are 5 data points to search
through, is there a way of speeding this up as well? or a better worksheet
function?

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have ToolsOptionsViewZero values checked ?

As for making you search easier:
If you can sort the range "RCSLData" on the worksheet (or sort a copy), then
..Find/VlookUp the range that match the first widest criteria, you will have
narrowed your search considerable. Work your way through your criteria
searching a narrowing range, until you find a match, or run out of rows.
Array are very fast and you can achieve the same as above, but you have do
all the work yourself.

NickHK

"Steven Cheng" wrote in message
...
I have a UDF that goes like this:

Private Function GetValue(Account As String, DeptID As String, DayofPeriod
As Integer, Period As Integer, FiscalYear As Integer) As Single
Dim rcslarray
Dim tempholdingcell As Double
Dim lastrow As Integer

rcslarray = [RCSLData]

GetValue = 0
For lastrow = 2 To UBound(rcslarray, 1)
If rcslarray(lastrow, 2) = Period And rcslarray(lastrow, 3) =
DeptID
And rcslarray(lastrow, 4) = Account And rcslarray(lastrow, 1) = FiscalYear
Then
If Mid(Account, 1, 1) = 3 Then
tempholdingcell = rcslarray(lastrow, DayofPeriod + 4) * -1
Else
tempholdingcell = rcslarray(lastrow, DayofPeriod + 4)
End If
End If
Next
GetValue = tempholdingcell
End Function

basically, its take a range passing it into an array, finding the values I
want from the parameters passed over by the funciton, and returning the
value
I want.

problem is when it is returning a zero value, although the formating is
correct in the cells, it shows up as $0 rather than $- as it should be. I
tested the value to see if it returned a 0 value to find that there is
some
"rounding" or lack of precision. is there anyway that i can get this done
within the function?

PS. I tried the rounding tempholdingcell but that didn't work and I have
tried to alter the data type of GETValue from double to single.

PSS. since the range is rather large and there are 5 data points to
search
through, is there a way of speeding this up as well? or a better
worksheet
function?

thanks NickHK. I will check out the .text and .value for the problem cells.

as for the easier search, how do I find/vlookup to narrow the range that I
am looking through?

"NickHK" wrote:

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have ToolsOptionsViewZero values checked ?

As for making you search easier:
If you can sort the range "RCSLData" on the worksheet (or sort a copy), then
..Find/VlookUp the range that match the first widest criteria, you will have
narrowed your search considerable. Work your way through your criteria
searching a narrowing range, until you find a match, or run out of rows.
Array are very fast and you can achieve the same as above, but you have do
all the work yourself.

NickHK

"Steven Cheng" wrote in message
...
I have a UDF that goes like this:

Private Function GetValue(Account As String, DeptID As String, DayofPeriod
As Integer, Period As Integer, FiscalYear As Integer) As Single
Dim rcslarray
Dim tempholdingcell As Double
Dim lastrow As Integer

rcslarray = [RCSLData]

GetValue = 0
For lastrow = 2 To UBound(rcslarray, 1)
If rcslarray(lastrow, 2) = Period And rcslarray(lastrow, 3) =
DeptID
And rcslarray(lastrow, 4) = Account And rcslarray(lastrow, 1) = FiscalYear
Then
If Mid(Account, 1, 1) = 3 Then
tempholdingcell = rcslarray(lastrow, DayofPeriod + 4) * -1
Else
tempholdingcell = rcslarray(lastrow, DayofPeriod + 4)
End If
End If
Next
GetValue = tempholdingcell
End Function

basically, its take a range passing it into an array, finding the values I
want from the parameters passed over by the funciton, and returning the
value
I want.

problem is when it is returning a zero value, although the formating is
correct in the cells, it shows up as $0 rather than $- as it should be. I
tested the value to see if it returned a 0 value to find that there is
some
"rounding" or lack of precision. is there anyway that i can get this done
within the function?

PS. I tried the rounding tempholdingcell but that didn't work and I have
tried to alter the data type of GETValue from double to single.

PSS. since the range is rather large and there are 5 data points to
search
through, is there a way of speeding this up as well? or a better
worksheet
function?