Formatting VBA

[ronicaroman]

I'd like my code to find any number in column A which starts with 3 (example 300049588007) and format it to add a 0 before the number (example 0300049588007). How can I do this?

 

[ronicaroman]

Nevermind, figured it out:

Sub AssignmentNumber()

'Adding two columns
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B2").Select
'Copy text from A into B (Assignment Number Header)
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

'Edit Assingment number - inserting the formula
'Colum B

Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-1]>60000000000, TEXT(RC[-1],""0000000000000""), RC[-1])"

'Paste special into column C
Columns("B:B").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Delete extra columns
Columns("A:B").Select
Selection.Delete

 

[BrianExcel]

You should be able to consolidate that code substantially to make it easier.

For example:

Sub CommandButton1_Click()
Dim r As Range, rAll As Range

With Sheet1
    Set rAll = Range(Cells(1, 1), Cells(2, 1).End(xlDown))
    
    For Each r In rAll
        If Left(r, 1) = "7" Then
            r = "3" & r
        End If
    Next
End With
End Sub

 

[BrianExcel]

One more comment - the code I provided is written to update values starting with a 7 with a 3 in front of it.

The below code is what you were trying for:

Sub CommandButton1_Click()
Dim r As Range, rAll As Range

With Sheet1
    Set rAll = Range(Cells(1, 1), Cells(2, 1).End(xlDown))
    
    For Each r In rAll
        If Left(r, 1) = "3" Then
            r = "0" & r
        End If
    Next
End With
End Sub

Also - since Excel automatically removes leading zeros, you will need to ensure whatever column you are working in is formatted as "Text" so the zeros show.