Hello,
I'm getting a #NAME? error when I try to insert this code into a much longer Macro, but it works perfectly when I just do it on its own. For reference, there are messy numbers in column V that need to be formatted into (XXX) XXX-XXXX.
Function PhoneFormat(Phone As String) As String
Dim i As Integer, sFormat As String, sCur As String, sTrunc As String, n As Integer
sTrunc = Phone
For i = 1 To Len(sTrunc)
If IsNumeric(Mid(sTrunc, i, 1)) Then n = n + 1
Next i
If n > 10 Then sTrunc = Right(sTrunc, Len(sTrunc) - 1)
For i = 1 To Len(sTrunc)
sCur = Mid(sTrunc, i, 1)
If IsNumeric(sCur) Then
Select Case Len(sFormat)
Case 0: sFormat = "(" & sCur
Case 3: sFormat = sFormat & sCur & ") "
Case 8: sFormat = sFormat & sCur & "-"
Case Else: sFormat = sFormat & sCur
End Select
End If
Next i
PhoneFormat = sFormat
End Function
Sub PhoneColumn()
Dim r As Long, c As Integer, i As Long, iLast As Long
Range("V1").Select
r = ActiveCell.Row
c = ActiveCell.Column
iLast = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
ActiveCell.Offset(0, 1).EntireColumn.Insert
Range(Cells(2, c + 1), Cells(iLast, c + 1)).FormulaR1C1 = "=PhoneFormat(RC[-1])"
Range("W:W").EntireColumn.Copy
Range("V1").PasteSpecial (xlPasteValues)
Range("W:W").EntireColumn.Delete
Range("V1").Value = "Phone Number"
End Sub
I'm getting a #NAME? error when I try to insert this code into a much longer Macro, but it works perfectly when I just do it on its own. For reference, there are messy numbers in column V that need to be formatted into (XXX) XXX-XXXX.
Function PhoneFormat(Phone As String) As String
Dim i As Integer, sFormat As String, sCur As String, sTrunc As String, n As Integer
sTrunc = Phone
For i = 1 To Len(sTrunc)
If IsNumeric(Mid(sTrunc, i, 1)) Then n = n + 1
Next i
If n > 10 Then sTrunc = Right(sTrunc, Len(sTrunc) - 1)
For i = 1 To Len(sTrunc)
sCur = Mid(sTrunc, i, 1)
If IsNumeric(sCur) Then
Select Case Len(sFormat)
Case 0: sFormat = "(" & sCur
Case 3: sFormat = sFormat & sCur & ") "
Case 8: sFormat = sFormat & sCur & "-"
Case Else: sFormat = sFormat & sCur
End Select
End If
Next i
PhoneFormat = sFormat
End Function
Sub PhoneColumn()
Dim r As Long, c As Integer, i As Long, iLast As Long
Range("V1").Select
r = ActiveCell.Row
c = ActiveCell.Column
iLast = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
ActiveCell.Offset(0, 1).EntireColumn.Insert
Range(Cells(2, c + 1), Cells(iLast, c + 1)).FormulaR1C1 = "=PhoneFormat(RC[-1])"
Range("W:W").EntireColumn.Copy
Range("V1").PasteSpecial (xlPasteValues)
Range("W:W").EntireColumn.Delete
Range("V1").Value = "Phone Number"
End Sub