Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Fractions, Feet, Inches, & macros
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Fractions, Feet, Inches, & macros
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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default Fractions, Feet, Inches, & macros
In your macro make this change:

NbrFeet = CInt(FeetIn)

I got result of 865 with D3 as input.

" wrote:

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.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Fractions, Feet, Inches, & macros
That works! Thanks alot

"Toppers" wrote:

In your macro make this change:

NbrFeet = CInt(FeetIn)

I got result of 865 with D3 as input.

" wrote:

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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Fractions, Feet, Inches, & macros
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))

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default Fractions, Feet, Inches, & macros
What's in D1,D2 and A5?

" wrote:

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))

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default Fractions, Feet, Inches, & macros
Hi,
try this in LENTEXT:

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

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

" wrote:

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))

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Fractions, Feet, Inches, & macros
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.

"Toppers" wrote:

What's in D1,D2 and A5?

" wrote:

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))

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Fractions, Feet, Inches, & macros
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?

"Toppers" wrote:

Hi,
try this in LENTEXT:

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

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

" wrote:

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))

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there a formula for inches to feet and inches? Excel Worksheet Functions 2 August 12th 06 12:45 AM
feet, inches 100pinesfarm Excel Worksheet Functions 2 December 13th 05 03:33 PM
feet and inches steve Excel Worksheet Functions 2 September 26th 05 05:34 PM
is there a macro that will convert from inches to feet and inches cable guy Excel Discussion (Misc queries) 1 June 20th 05 07:29 PM
Converting inches to feet & inches. svech61 Excel Worksheet Functions 2 November 27th 04 04:23 AM


All times are GMT +1. The time now is 10:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement