adding a formula to a macro

[Hemming]

I have a column of telephone numbers. I need to change the area code an
first three digits of the number to a new area code and new first thre
digits. I need to do this for the entire column. example: 555555555
change to (444)444-5555. the only part of the number that will stay th
same is the last 4 digits. if there are 100 different telephone number
there will be 100 unique last 4 digits. the formul
=value("444444"&right(a1:a100,4)) works well as a formula and changin
the cell format to special, telephone number , but I really wanted t
use in a marco. can anyone help .....thank

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
myRng.NumberFormat = "[<=9999999]###-####;(###) ###-####"

For Each myCell In myRng.Cells
With myCell
If IsEmpty(myCell.Value) Then
'do nothing
Else
.Value = 444444 & Right(.Text, 4)
End If
End With
Next myCell
End Sub