Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Worksheet vbs to show 0.00 values as "-"

Hi Expert

How would you use vba to make all the values in a range of cells
From 0.00 to "-"....

Thanks
0
route217
Asked:
route217
1 Solution
 
gman84Commented:
The follow should help you:
    Dim iCol As Integer
    Dim iRow As Integer
    
    For iRow = 1 To 10
        For iCol = 1 To 1
            If (IsNumeric(Cells(iRow, iCol)) And Cells(iRow, iCol) = 0) Or Trim$(Cells(iRow, iCol)) = vbNullString Then
                Cells(iRow, iCol) = "-"
            End If
        Next
    Next

Open in new window

0
 
route217Author Commented:
Many thanks for the feedback..
0
 
byundtCommented:
I realize that the question has already been answered, but you could more simply change the numeric format to display 0 as -.

Range("A2:A100").NumberFormat = "0.00;-0.00;-;@"             'Puts the hyphen at far right
Range("A2:A100").NumberFormat = "0.00;-0.00;-_0_0;@"      'Puts the hyphen where decimal point goes

The benefit of changing the numeric format (rather than the value in the cell) is that you can still use the cell value in other arithmetic computations. Changing the value in the cell to text (as in the Accepted Solution) can sometimes cause an error value to result in subsequent calculations.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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