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