Add '1' infront of cells with 9 digits in a column

[Russmeister57]

Hi all,

I have been reviewing but cant quite find the answer that i need.
I need a macro to search column 'J' in 'Sheet1!' for each cell that contains 9 digits and for it to add the number 1 in front of all these cells that contain 9 digits.
Column J will have in it all numerical digits ranging from either 0, 9 or 10 digits in each cell.

Any help would be greatly appreciated.

 

[Micron]

Don't know how you want to trigger the code, but this should work

Sub AddAOne()
Dim Lrow As Long, n As Long
Dim rng As Range

Lrow = Cells(rows.count, "J").End(xlUp).Row
For n = 2 To Lrow
    Set rng = Cells(n, 10)
    If Len(rng) = 9 Then rng = 1 & rng
Next
Set rng = Nothing

End Sub

You might need it to disable other events because that could trigger other events when cells change.

 

[Russmeister57]

Don't know how you want to trigger the code, but this should work

Sub AddAOne()
Dim Lrow As Long, n As Long
Dim rng As Range

Lrow = Cells(rows.count, "J").End(xlUp).Row
For n = 2 To Lrow
    Set rng = Cells(n, 10)
    If Len(rng) = 9 Then rng = 1 & rng
Next
Set rng = Nothing

End Sub

You might need it to disable other events because that could trigger other events when cells change.

That is perfect, thank you so much

 

[Micron]

Glad I could help. If you have a solution it's customary to mark it that post as the solution so that it comes off the list of threads that need attention. It also advises others that worked, which may help them in the future.

 

[rlv01]

One more:

Sub AddOne()
    Dim R As Range, S As String
    
    With Worksheets("Sheet1")
        For Each R In .Range("J2", .Range("J" & .Rows.Count).End(xlUp))  'Assumes there is a header row
            S = CStr(Trim(R.Value))
            If VBA.IsNumeric(S) And Len(S) = 9 Then
                R.Value = "1" & S
            End If
        Next R
    End With
End Sub

 

[Peter_SSs]

Column J will have in it all numerical digits ranging from either 0, 9 or 10 digits in each cell.

If that is the case then this might be another option that does them all at once rather than line-by-line.

Sub NineToTenDigits()
  With Range("J2", Range("J" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#)=9,1&#,if(#="""","""",#))", "#", .Address))
  End With
End Sub