Prev: Excel 2007 "Protect Sheet" option not available
Next: Search and display based on cell value
From: Dana DeLouis on 15 May 2010 00:05
On 5/14/2010 9:25 AM, Mrs. Robinson wrote:
> How do I go from this formula
> =INT(G20/12)& " ft. "& MOD(G20,12)& " in."
> to a line in VBA that will perform in this function:
>
> Function ConvertInches(intInches)
>
> Dim intInches As Integer
> Dim intFeet As Integer
>
> ConvertInches = INT(intInches/12)& " ft. "& MOD(intInches,12)& " in."
>
> End Function
>
> I know I'm screwing it up but hopefully it's a simple fix.
>
> Thanks,


>> Int(intInches / 12)

Another option:

intInches=26

?Int(intInches / 12)
2

or...

?intInches\12
2



Not the best, but something different if the Op doesn't mind a leading
zero for the inches...

Sub Test()
Debug.Print Inch2FtIn(33)
End Sub

2 ft. 09 in

Function Inch2FtIn(x)
Inch2FtIn = Format(WorksheetFunction.DollarFr(x / 12, 12), _
"## \f\t. 00 \i\n")
End Function


= = = = = = =
Dana DeLouis
From: Rick Rothstein on 15 May 2010 00:23
IF the inches argument to the function could possibly contain a fraction
component (as Mike has suggested could be the case), then you might not want
to use integer division as you have suggested. If floating point numbers are
involved, integer division may not return the answer you expect. Most people
think x\y is short-hand for Int(x/y), probably because of its name "integer"
division. However, VB applies Banker's Rounding to the numerator AND
denominator BEFORE performing the division. Many would think that this...

MsgBox 4.5 \ 1.5

should display 3 in the MessgeBox, however, it prints out 2 instead.
Banker's Round rounds the 4.5 to 4 (the nearest even number) and rounds 1.5
to 2 (again, the nearest even number) AND ONLY THEN does it do the division
and truncate the fractional part (4/2 producing 2 as the answer).

--
Rick (MVP - Excel)



"Dana DeLouis" <delouis(a)bellsouth.net> wrote in message
news:OKmVmP#8KHA.5716(a)TK2MSFTNGP06.phx.gbl...
> On 5/14/2010 9:25 AM, Mrs. Robinson wrote:
>> How do I go from this formula
>> =INT(G20/12)& " ft. "& MOD(G20,12)& " in."
>> to a line in VBA that will perform in this function:
>>
>> Function ConvertInches(intInches)
>>
>> Dim intInches As Integer
>> Dim intFeet As Integer
>>
>> ConvertInches = INT(intInches/12)& " ft. "& MOD(intInches,12)& "
>> in."
>>
>> End Function
>>
>> I know I'm screwing it up but hopefully it's a simple fix.
>>
>> Thanks,
>
>
> >> Int(intInches / 12)
>
> Another option:
>
> intInches=26
>
> ?Int(intInches / 12)
> 2
>
> or...
>
> ?intInches\12
> 2
>
>
>
> Not the best, but something different if the Op doesn't mind a leading
> zero for the inches...
>
> Sub Test()
> Debug.Print Inch2FtIn(33)
> End Sub
>
> 2 ft. 09 in
>
> Function Inch2FtIn(x)
> Inch2FtIn = Format(WorksheetFunction.DollarFr(x / 12, 12), _
> "## \f\t. 00 \i\n")
> End Function
>
>
> = = = = = = =
> Dana DeLouis

First  |  Prev  | 
Pages: 1 2 3
Prev: Excel 2007 "Protect Sheet" option not available
Next: Search and display based on cell value