How to change a cell from "General" to "Number" and add comma?

Question

How to change a cell from "General" to "Number" and add comma?

Navigation

#1 by (1 votes)

2

I'm learning VBA and I had a hard time translating a column that has values formatted as "General" for number, moreover, I need those values to be expressed with a comma.

Current values (sample):

I know there is a simpler way to do this without using VBA, but I would like to learn and acquire more knowledge in the tool.

vba excel-vba

asked by anonymous 29.09.2017 / 16:10

1 answer

Component select disabled and angular selected value TextBoxFor with whitespace

score 1 · Accepted Answer

To format, the Range.NumberFormat property is used.

Dim rng As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Planilha1")
Set rng = ws.Range("B:B", "E:E")

With rng
    .NumberFormat = "#,##0.00"
End With

Explanation of the code

Worksheet

Declare the worksheet to be used in VBA

 Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Planilha1")

Range

Declare the range to be used, in this case, columns B and E.

 Dim rng As Range: Set rng = ws.Range("B:B", "E:E")

Logic With (com)

Use with to accomplish what's inside With and End With only within range rng

  With rng
  End With

Format

Formatting for numbers with two decimal places

.NumberFormat = "#,##0.00"

Another way to use and declare this same code

Dim rng As Range

Set rng = Planilha1.Range("B:B", "E:E")

rng.NumberFormat = "#,##0.00"

Check the NumberFormat

To check some cell formats, click More Numbers Formats

ChecktheCustomCategoryforsomeexamplesofNumberFormat