Re: Formating and Precision...I think

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"Steven Cheng" <StevenCheng@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FAFB0424-1909-431E-A7A4-38FF9E9FBF3C@xxxxxxxxxxxxxxxx
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/scriptcenter/resources/officetips/jun05/tips0607.mspx

NickHK


"Steven Cheng" <StevenCheng@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D009B442-F393-479E-9482-B234D1676A99@xxxxxxxxxxxxxxxx
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" <StevenCheng@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:D43E9D0A-3853-4C05-A86C-660FCB755E39@xxxxxxxxxxxxxxxx
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 Tools>Options>View>Zero values checked ?

As for making you search easier:
If you can sort the range "RCSLData" on the work*** (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" <StevenCheng@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:42BC9531-982F-45AE-A183-CEA50550A3F7@xxxxxxxxxxxxxxxx
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
work***
function?











.

Quantcast