Formatting Data to 2 Decimal Places in VBA

[kmh6278]

In my workbook, cells(9,3) = 39.66

The code below is returning facility_1 = 40.00

I need the code to return 39.66

I also attempted to use FormatNumber(cells(9,3),2), which also yielded 40 as a result.

Any suggestions would be greatly appreciated!

Sub SetDataCells()

Dim facility_1 As Integer


facility_1 = Format(Cells(9, 3), "fixed")

End Sub

 

[excel-proficient]

Try:

Sub rounding()
ActiveCell.Select
Selection.Value = Format(ActiveCell, "#.00")

End Sub

 

[Phox]

You declared the variable facility_1 as an integer. That means it can only be a whole number. Declare it as Double instead.

 

[TheNoocH]

biggest problem is you have facility_1 set to integer...can't have decimal points in an integer....

try this

Sub SetDataCells()

Dim facility_1 As double
facility_1 = Format(Cells(9, 3).Value, "0.00")
MsgBox facility_1

End Sub

 

[kmh6278]

Thanks! Changing the declaration to "double" was the only edit needed. Much appreciated.

 

[kylefoley76]

I'm having this problem too but I can't get it to work. When I debug and stop the code I press debug.print in the immediate window and I get .75 for the selectionvalue but that value will not transfer to k

Dim j As Integer, m As Double, k As Double


Range("i266").Select
m = 0


Do Until activecell.Offset(1).Value = "stop"


    activecell.Offset(1).Select
    j = activecell.Value
    If j = 1 Then
        activecell.Offset(0, 4).Select
        Selection.Value = Format(activecell, "0.00")
        k = Selection.Value
        m = m + k
        activecell.Offset(0, -4).Select
    End If
    If j = 0 And m > 0 Then
        activecell.Offset(0, -1).Value = m
        m = 0
    End If
    
Loop

 

[kylefoley76]

problem solved. It turns out that I had a zero in the cell in question rather than .75.

 

[PeterMole]

I came across this thread while trying to display a currency when the data was an integer (i.e. data 76 but want to show 76.00).

My main issue was using the Double data type, the Currency data type, try as I may I couldn't get the value to show with 2 decimals.

After looking at various data types I found that the Format function works perfectly with the String data type.

If there is text as the value and not a number then it just ignores the format and displays the text even tough it has a data type of String so logically makes no sense to me, I though it would error!!

After a little back and forth between posts I came up with the following;

Sub LittleTestOnDecimals()

   'Declare a few things

   Dim myValue as [B]String[/B] 'The value I want to convert
   Dim myMsg as String 'The value transposed into the format I want to see

   myValue = Worksheet("SomeReference").Range("A1").Value ' Get the value

   myMsg = Format(myValue, "#.00") ' Transforming the value into the format you want

   MsgBox (myMsg) 'Show me the results

End Sub

Not to sure if this programatically correct but it worked for me after pulling my hair our trying to resolve and hope it helps someone save a little hair!!

 

[peten]

After looking at various data types I found that the Format function works perfectly with the String data type.

Not to sure if this programatically correct but it worked for me after pulling my hair our trying to resolve and hope it helps someone save a little hair!!

It did, thanks a lot! I had the same issue and was cautious of using anything but Value data type until saw your post...