Solved

Format cells programmatically

Posted on 2014-02-04
4
176 Views
Last Modified: 2014-02-04
I have a range of cells in an Excel worksheet, columns B through Z and rows 2 through 100. I need to set the format of each cell in that range to numeric, no decimals and negative numbers represented by parenthesis instead of a minus sign. How do I do it programmatically?  I already have a macro that does some other stuff. I just need to add a routine to it to accomplish what I just described. Thank you.
0
Comment
Question by:dbfromnewjersey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 75 total points
ID: 39832745
Here is the code:

Range("B2:Z100").Select
Selection.NumberFormat = "0_);(0)"
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 75 total points
ID: 39832758
It's a one-line macro:
Sub Formatter()
Range("B2:Z100").NumberFormat = "#;(#);0;@"
End Sub

Open in new window

0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 350 total points
ID: 39832766
In line with the last question, and run the same way:

Sub ChangeNumberFormat()
Dim cell As Range
On Error Resume Next
    For Each cell In Selection
        cell.NumberFormat = "#;(#);0;@"
    Next
End Sub

Open in new window


or if you are wanting to combine the operations for each selection made:

Sub FillEmptyandChangeFormat()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = "0"
    For Each cell In Selection
        If IsEmpty(cell) Then
            cell.Value = InputValue
        End If
    Next
    For Each cell In Selection
        cell.NumberFormat = "#;(#);0;@"
    Next
End Sub

Open in new window

0
 

Author Comment

by:dbfromnewjersey
ID: 39832849
Thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question