Menu
I've looked around for this and it seems so simple, yet I can't get it to work.
I have a table and one column needs to be formatted as a percentage. Below is my code but it is not formatting the cells, it just leaves them as the decimal.
I think this is because cell, even though declared as a range, is actually the value of the cell so I don't know how to refer to that range.
My returnRebate variable is declared as a range and the loop is looping through the correct range.
Code:
Dim cell As Range, p As Double
For Each cell In returnRebate
p = cell.Value
If p <> 0 And p > 1 Then
p = p * 0.01
cell.Value = p
cell.NumberFormat = "Percent" 'Not formatting here
ElseIf p < 1 And p > 0 Then
'do nothing to it
Else
cell.Value = vbNullString
End If
Next
728
Display numbers as percentagesOn the Home tab, in the Number group, click the icon next to Number to display the Format Cells dialog box. In the Format Cells dialog box, in the Category list, click Percentage. In the Decimal places box, enter the number of decimal places that you want to display.
The VBA FORMATPERCENT function is listed under the data type conversion category of VBA functions. When you use it in a VBA code, it returns the supplied expression by applying a percentage format to it. In simple words, it returns the result with with a percentage format as a string data type.
Use the FindFormat or ReplaceFormat properties of the Application object to return a CellFormat object. Use the Borders, Font, or Interior properties of the CellFormat object to define the search criteria for the cell format.
Now go to VBA editor and create a macro name. After selecting the cell to select the property called “NumberFormat” by putting dot (.) After selecting the property put an equal sign. Now apply the format we wish to apply in this case, format is date format i.e. “dd-mmm-yyyy” format.
Try replacing .NumberFormat='Percent' with .NumberFormat="0.00%"
137
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
