View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
homer homer is offline
external usenet poster
 
Posts: 47
Default Display feet inches and fraction

When I add the following ........)-1)+((-1)*(MID...... the formula works.
It was subtracting the fraction from the whole number.


Two things I have encountered and cannot find the solution is in the VBA.
If I put text into a cell and erase the text or clear contents, an inch mark
is left. This cannot be removed unless I am in design mode.

When I try to copy one cell to another I get a popup with Run-time error
'13': Type mismatch. When I click on Debug the line that is highlighted is
TargetPattern = Target.Value.

I wonder if the two are related? Any idea what could be causing them?

Thanks,
Homer


"Homer" wrote:

So far so good. Now I need to be able to convert the dimensions to decimals
so I can add and multiply the various lengths. I found a reply from a month
a go with the subject: Subject: change feet/ inches to decimals
Where a link was given to cpearson.com which contained a formula
=LEFT(A1,FIND("'",A1)-1)+((MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1))/12)
when I put this formula into a cell I get 10.70833 instead of 11.29167 for
11'-3 1/2". I have been through the formula and cannot figure out what is
wrong.

"Rick Rothstein (MVP - VB)" wrote:

Yes, I mistyped the number of inch-marks... 4 of them is what I meant.

Rick


"Homer" wrote in message
...
Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?

Right-click the sheet tab for the sheet you want this functionality on
and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick