Formating and Precision...I think

[Steven Cheng]

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?

 

[NickHK]

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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]

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?

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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?

 

[NickHK]

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list that
you search. You can down further .finding the second criteria only in the
range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value <> Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value = Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue>
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """, DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the function's
result.
The workbook holding the data would need to closed though and be in a table
format.

NickHK

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?

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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 Cheng]

NickHK;

I didn't think of this. Is ADO accessible in Excel without referencing to
an Access library file?

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list that
you search. You can down further .finding the second criteria only in the
range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value <> Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value = Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue>
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """, DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the function's
result.
The workbook holding the data would need to closed though and be in a table
format.

NickHK

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?

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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?

 

[NickHK]

Steven,
You don't need Access, just a reference to "Microsoft ActiveX Data Objects
x.x Library".
Your Excel data needs to be in a regular table structure for this to work
correctly.
Then just query the file with SQL.

Here's one:
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

NickHK

NickHK;

I didn't think of this. Is ADO accessible in Excel without referencing to
an Access library file?

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list that
you search. You can down further .finding the second criteria only in the
range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value <> Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value = Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue>
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """, DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the function's
result.
The workbook holding the data would need to closed though and be in a table
format.

NickHK

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?

:

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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 Cheng]

oh...I see thank you very much. I didn't realize that I could do this
without Access....

Steven,
You don't need Access, just a reference to "Microsoft ActiveX Data Objects
x.x Library".
Your Excel data needs to be in a regular table structure for this to work
correctly.
Then just query the file with SQL.

Here's one:
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

NickHK

NickHK;

I didn't think of this. Is ADO accessible in Excel without referencing to
an Access library file?

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list that
you search. You can down further .finding the second criteria only in the
range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value <> Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value = Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue>
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """, DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the function's
result.
The workbook holding the data would need to closed though and be in a table
format.

NickHK

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?

:

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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 Cheng]

Sorry, just one more thing...what is the differences between the different
ActiveX Data Object libraries....just different versions/updates along the
way? also, I see one called ActiveX Data Objects Recordsets 2.7 Library?
what is this for?

Steven,
You don't need Access, just a reference to "Microsoft ActiveX Data Objects
x.x Library".
Your Excel data needs to be in a regular table structure for this to work
correctly.
Then just query the file with SQL.

Here's one:
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

NickHK

NickHK;

I didn't think of this. Is ADO accessible in Excel without referencing to
an Access library file?

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list that
you search. You can down further .finding the second criteria only in the
range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value <> Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value = Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue>
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """, DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the function's
result.
The workbook holding the data would need to closed though and be in a table
format.

NickHK

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?

:

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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?

 

[NickHK]

Steven,
Yes, you probably have a number of versions of ADO.
Depends who else will be using your workbook. If it's only for you, use the
latest.
If you will distributing to a lot of unknown users, may be the oldest.
To me, there not a great deal of difference between them in the general
sense.
Here a brief list of ADO version/OS
http://www.teratrax.com/articles/mdac_ado.html

ActiveX Data Objects Recordsets 2.7 Library:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q183606
but I guess you do not need this.


NickHK

Sorry, just one more thing...what is the differences between the different
ActiveX Data Object libraries....just different versions/updates along the
way? also, I see one called ActiveX Data Objects Recordsets 2.7 Library?
what is this for?

Steven,
You don't need Access, just a reference to "Microsoft ActiveX Data Objects
x.x Library".
Your Excel data needs to be in a regular table structure for this to work
correctly.
Then just query the file with SQL.

Here's one:
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

NickHK

NickHK;

I didn't think of this. Is ADO accessible in Excel without referencing to
an Access library file?

:

Steven,
As long as your data in the range "RCSLData" is sorted on your first
criteria, say Period, the you can cut down on the amount of the list that
you search. You can down further .finding the second criteria only

in
the

range that matches the first etc. Something like
Dim PeriodStart As Range
Dim i As Long
Set PeriodStart = Range("RCSLData").Columns(1).Find(what:=Period)
With PeriodStart
Do Until .Offset(i, 0).Value <> Period
If .Offset(i, 1).Value = DeptID And .Offset(i, 2).Value = Account
And .Offset(i, 2).Value = FiscalYear Then
GetValue=<YourValue>
Exit Function
End If
i = i + 1
Loop
End With
GetValue = CVErr(xlErrNA)

To me though the easiest way would be to use ADO and just issue an SQL
statement, leaving the hard work up to the db components.
"SELECT FinalValue FROM RCSLData WHERE Account= """ & AccStr & """, DeptID="
& DeptID & ", DayofPeriod=" DayofPeriod.....etc
You can further process any records returned before returning the function's
result.
The workbook holding the data would need to closed though and be in

a
table

format.

NickHK

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?

:

Steven,
Not sure what you mean about the formatting problem.
What is .Value and .Text of the problem cell(s) ?
Do you have Tools>Options>View>Zero 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

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?