Solved

Format cells programmatically

Posted on 2014-02-04
4
167 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

770 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