asked on

Excel Formula on selected range

I have a macro that formats a spreadsheet.
In that macro I want to take the values in 3 columns and divide them by 100 then format as percentage.
Can someone help me get this correct?

Columns("S:U") Select
**What do I add to divide all values in row 2 and below by 100**
Selection.NumberFormat = ("0.0%")

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

This will however do all cells in the columns, so if 2007 or later over 1m cells per column and ust noticed the Row 2 and below criteria, try this instead:

For Each Cell in Selection
If And Cell.Row > 1  Cell.Value <> "" Then
Cell.Value = Cell.Value/100
Else
End If
Next Cell

Open in new window

It will still check all cells in the columns but will ignore the blanks. Would be worth restricting to a range rather than the whole column, this can be done as a dynamic range rather than hard coded but would need more info to determine the last row of data.

Cheers
Rob H

Rob Henson

Typo - move the And between the two criteria, was thinking formulaic rather than code.

Cheers
Rob H

leonstryker

I think the easiest thing to do would be to set a cell to 100, then Copy it, highlight the range you want to modify, then use Paste Special/Divide. Once this is done yo can apply formating.

leonstryker

Set A1 to 100

Range("A1").Copy
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks:=True

Mitch Swetsky

ASKER

Thank so much. Works great.