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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With