On Tue, 05 Apr 2005 10:57:08 -0700, Darin Kramer
wrote:

The problem is it works FINE on my machine, but when looked at by
someone else, the . is changed to a , and the formulae produces a #value
error. Im going insane trying to get it to work in both enviornments.
Any thoughts on how to achieve a consistent solution (ie works on both
machines) would be welcomed.

Would a VBA solution be acceptable?

Check this out, but I think that VBA is US-centric. So it may interpret input
as US and give output in the local format.

For example, the simple UDF:

======================
Function GetVal(rg As Range) As Double
If IsNumeric(rg.Value) Then
GetVal = rg.Value
Else
GetVal = Val(rg)
End If
End Function

======================

given a cell containing 12.2a will return 12.2 with the regional settings set
to English(US) and will return 12,2 with the regional settings set to
Dutch(Belgium).

If this is OK, to enter the UDF, <alt-F11 opens the VB Editor. Ensure your
project is highlighted in the Project Explorer Window, then Insert/Module and
paste the above code into the window that opens.

To use this, enter the formula

=getval(cell_ref)

into some cell where cell_ref is contains your value.

But be sure to check all the various possible contents of cell_ref under the
different regional settings to ensure you get the output you desire.

For example, if you are using Dutch settings and have the text string 12.2a in
cell_ref, the function will return the number 12,2 as you desire. If the
contents is the number 12,2 it will return 12,2 also. But if the contents is
the text string 12.2, the function will return the number 122,0

So if the above scenario is possible, we may need a different approach.



--ron