Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to format column to number format in Excel sheet?

Tags:

excel

vba

Below is the VBA code. Sheet2 contains all of the values in general format. After running the code, values in column 'C' of Sheet3 contain exponential values for numbers which are 13 or more digits.

What should be done so that column 'C' of Sheet3 does not contain exponential values?

private Sub CommandButton1_Click()
Dim i, j, k As Variant

 k = 1
For i = 1 To 30000
If Sheet2.Range("c" & i).Value >= 100 And Sheet2.Range("c" & i).Value < 1000 Then
Sheet3.Range("a" & k).Value = Sheet2.Range("a" & i).Value
Sheet3.Range("b" & k).Value = Sheet2.Range("b" & i).Value

Sheet3.Range("c" & k).Value = Sheet2.Range("c" & i).Value

k = k + 1
End If
Next

End Sub
like image 301
Punith GP Avatar asked Mar 17 '12 17:03

Punith GP


2 Answers

This will format column A as text, B as General, C as a number.

Sub formatColumns()
 Columns(1).NumberFormat = "@"
 Columns(2).NumberFormat = "General"
 Columns(3).NumberFormat = "0"
End Sub
like image 58
datatoo Avatar answered Nov 20 '22 06:11

datatoo


If your 13 digit "number" is really text, that is you don't intend to do any math on it, you can precede it with an apostrophe

Sheet3.Range("c" & k).Value = "'" & Sheet2.Range("c" & i).Value

But I don't see how a 13 digit number would ever get past the If statement because it would always be greater than 1000. Here's an alternate version

Sub CommandClick()

    Dim rCell As Range
    Dim rNext As Range

    For Each rCell In Sheet2.Range("C1:C30000").Cells
        If rCell.Value >= 100 And rCell.Value < 1000 Then
            Set rNext = Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Offset(1, 0)
            rNext.Resize(1, 3).Value = rCell.Offset(0, -2).Resize(1, 3).Value
        End If
    Next rCell

End Sub
like image 35
Dick Kusleika Avatar answered Nov 20 '22 07:11

Dick Kusleika