Formating and Precision...I think
 

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?

Formating and Precision...I think
 

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?

Formating and Precision...I think
 

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?

Formating and Precision...I think
 

NickHK;

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

"NickHK" wrote:

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

"Steven Cheng" wrote in message
...
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?

Formating and Precision...I think
 

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/scr.../tips0607.mspx

NickHK


"Steven Cheng" wrote in message
...
NickHK;

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

"NickHK" wrote:

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

"Steven Cheng" wrote in message
...
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?

Formating and Precision...I think
 

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

"NickHK" wrote:

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/scr.../tips0607.mspx

NickHK


"Steven Cheng" wrote in message
...
NickHK;

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

"NickHK" wrote:

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

"Steven Cheng" wrote in message
...
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?

Formating and Precision...I think
 

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?

"NickHK" wrote:

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/scr.../tips0607.mspx

NickHK


"Steven Cheng" wrote in message
...
NickHK;

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

"NickHK" wrote:

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

"Steven Cheng" wrote in message
...
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?

Formating and Precision...I think
 

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...;EN-US;q183606
but I guess you do not need this.


NickHK

"Steven Cheng" wrote in message
...
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?

"NickHK" wrote:

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/scr.../tips0607.mspx

NickHK


"Steven Cheng" wrote in message
...
NickHK;

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

"NickHK" wrote:

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

"Steven Cheng" wrote in
message
...
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?