We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel Formula on selected range

Mitch Swetsky
on
Medium Priority
243 Views
Last Modified: 2012-05-11
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%")
Comment
Watch Question

Finance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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 HensonFinance Analyst
CERTIFIED EXPERT

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

Cheers
Rob H
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.
Set A1 to 100

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

Author

Commented:
Thank so much. Works great.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.