Fractions, Feet, Inches, & macros

[Guest]

2 cells. One for feet(A1), one for inches(A2). A3=(A1+(A2/12))
-Ok lets do the samething again.
2 more cells. One for feet(B1), one for inches(B2). B3=(B1+(B2/12))
-One more time
2 more cells. One for feet(C1), one for inches(C2). C3=(C1+(C2/12))
-Finally D3=((A3+B3)-C3)

Now as far as I can tell everything works just fine. But I have a macro to
convert a decimal number into Feet and Inches:

Public Function LenText(FeetIn As Double)
Denominator = 32
NbrFeet = Fix(FeetIn)
InchIn = (FeetIn - NbrFeet) * 12
NbrInches = Fix(InchIn)
FracIn = (InchIn - NbrInches) * Denominator
Numerator = Application.WorksheetFunction.Round(FracIn, 0)
If Numerator = 0 Then
FracText = ""
ElseIf Numerator = Denominator Then
NbrInches = NbrInches + 1
FracText = ""
Else
Do
If Numerator = Application.WorksheetFunction.Even(Numerator) Then
Numerator = Numerator / 2
Denominator = Denominator / 2
Else
FracText = " " & Numerator & "/" & Denominator
Exit Do
End If
Loop
End If
LenText = NbrFeet & "' " & NbrInches & FracText & """"
End Function

Try;
A1 = 865
A2 = 2
B1 = 0
B2 = 6.5
C1 = 0
C2 = 8.5

If done correctly D3 will return 865. If the run the LenText marco on this
cell it returns 864' 12". If you type 865 in a blank cell and run the
LenText macro against it, it returns 865' 0".

What gives? This doesn't make any sence to me. Is the Macro corrupt or is
it something wrong with decimal places?

I realize this is a lot to process, but any help would be very useful.

 

[Guest]

In your macro make this change:

NbrFeet = CInt(FeetIn)

I got result of 865 with D3 as input.

 

[Guest]

That works! Thanks alot

In your macro make this change:

NbrFeet = CInt(FeetIn)

I got result of 865 with D3 as input.

 

[Guest]

I take that back. Its not working like I had hoped. I also have another
macro that converts 1' 0" to 1;

Public Function Feet(LenString As String)
Dim FootSign As Integer
Dim InchSign As Integer
Dim SpaceSign As Integer
Dim FracSign As Integer
Dim InchString As String
Dim Word2 As String
LenString = Application.WorksheetFunction.Trim(LenString)
On Error Resume Next
FootSign = Application.WorksheetFunction.Find("'", LenString)
If IsEmpty(FootSign) Or FootSign = 0 Then
Feet = 0
FootSign = 0
Else
Feet = Val(Left(LenString, FootSign - 1))
End If

If Len(LenString) = FootSign Then Exit Function
InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign
+ 1))
InchSign = Application.WorksheetFunction.Find("""", InchString)
If Not IsEmpty(InchSign) Or InchSign = 0 Then
InchString = Application.WorksheetFunction.Trim(Left(InchString,
InchSign - 1))
End If

SpaceSign = Application.WorksheetFunction.Find(" ", InchString)
If IsEmpty(SpaceSign) Or SpaceSign = 0 Then
FracSign = Application.WorksheetFunction.Find("/", InchString)
If IsEmpty(FracSign) Or FracSign = 0 Then
Feet = Feet + Val(InchString) / 12
Else
Feet = Feet + (Val(Left(InchString, FracSign - 1)) /
Val(Mid(InchString, FracSign + 1))) / 12
End If
Else
Feet = Feet + Val(Left(InchString, SpaceSign - 1)) / 12
Word2 = Mid(InchString, SpaceSign + 1)
FracSign = Application.WorksheetFunction.Find("/", Word2)
If IsEmpty(FracSign) Or FracSign = 0 Then
Feet = "VALUE!"
Else
If FracSign = 0 Then
Feet = "VALUE!"
Else
Feet = Feet + (Val(Left(Word2, FracSign - 1)) /
Val(Mid(Word2, FracSign + 1))) / 12
End If
End If
End If
End Function


If you use this macro in conjection with the the other, changing Fix to CInt
gives mixed results. So instead of doing what I discribed early use this to
convert the feet and inches. =LenText((Feet(D1))+(Feet(D2))-Feet(A5))

 

[Guest]

What's in D1,D2 and A5?

 

[Guest]

Hi,
try this in LENTEXT:

NbrFeet = Fix(Round(FeetIn, 3)) (Change the 3 if required)

I "found" your problems (because Cint rounds up).

 

[Guest]

D1 = Known elevation point (like 100'-0")
D2 = Reading on Transit at Known Elevation point.
A5 = Reading on Transit stick at another location.

Basicly all this does is convert all your grade shots off of a known
elevation point. Used primarly for surveying site properties.

 

[Guest]

This appears to be working much better. I can't thank you enough.

Just so I'm 100% clear on this. 3 is equal to the number of decimal places
it will round to? Correct?

 

[Guest]

Yes; don't change it unless you do get further in data accuracy problems and
do keep it as small as possible.