VBA For Each cell In Range Format as Percentage

vba excel loops number-formatting percentage
anonymous picture anonymous · Mar 16, 2017 · Viewed 61.3k times · Source

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

Answer

RyanL picture RyanL · Mar 16, 2017

Try replacing .NumberFormat='Percent' with .NumberFormat="0.00%"

Related questions

Loop through each row of a range in Excel

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it. How do I loop through each row …

Read More
How to execute a function for each cell in a column and loop through all workbooks?

Here's what I have so far: Sub TrimColumnD() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Dim c As Range For Each c In ActiveSheet.UsedRange.Columns("D").Cells c.Value = WorksheetFunction.Trim(c.Value) Next c Next …

Read More
Excel VBA - Skip sub or end sub IF Statement

My code loops through cell values in a table and sees if document with same name exists. If it does exist, it will perform an action to open those files and import data. If bProcess Then FileCounter = 0 For Each folderIDX …

Read More