• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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%")
0
Mswetsky
Asked:
Mswetsky
  • 3
  • 2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
Try this:

For Each Cell in Selection

Cell.Value = Cell.Value/100

Next Cell

Open in new window


Cheers
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
Rob HensonIT & Database AssistantCommented:
Typo - move the And between the two criteria, was thinking formulaic rather than code.

Cheers
Rob H
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
leonstrykerCommented:
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.
0
 
leonstrykerCommented:
Set A1 to 100

Range("A1").Copy
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks:=True
0
 
MswetskyAuthor Commented:
Thank so much. Works great.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now