need vba to verify 10 numbers only

[gibsongk55]

Hi,

How could I use VBA in excel to verify a phone number. Only number 10 digits. If not 10 numbers or has hyphens or other characters, then alert me.

Thanks,

Gibs

 

[mikerickson]

Something like

If PhoneNumber Like "##########" Then
    Msgbox "Good"
Else
    MsgBox "bad"
End If

 

[gibsongk55]

Hi Mike,

Thanks so much for your response. Looks easy... i will have to get back into figuring these things out more. How would I do that to only msg me if not valid? If valid I don't want anything.


Thanks again,

Gibs

 

[mikerickson]

If Not (PhoneNumber Like "##########") Then
    MsgBox "bad"
End If

 

[Peter_SSs]

Do you need vba? Assuming the numbers are in a worksheet, could you just use Conditional Formatting to highlight any incorrect ones?

 

[gibsongk55]

Hi,

Yes i would like to use VBA and check a column. I thought the answer was too easy. I ran the macro and got nothing. How does it know what column or range to check?

Thanks,

Gibs

 

[Peter_SSs]

How does it know what column or range to check?

It doesn't - but you didn't tell us what column or range to check and we cannot see the sheet.

Also could you describe or provide some sample data so the we can get an idea of the sort of things that may be in the column or range (correct values and incorrect values)?

 

[gibsongk55]

Hi Peter,

Thank you for your help.

I want to check all cells in column T for a 10 digit numerical phone number with no parenthesis or hyphens.

So if a cell in Column T contains 10 numbers only I don't want anything. If a cell does not equal 10 numbers or has a character other than numerical I want a msg box with the row # or something i can identify where the problem is.

Example:
Column T
Row 1 2128773940
Row 2 7147938037
Row 3 303849487
Row 4 901737-324
Row 5 9148370088

Row 3 and 4 are invalid and should msg alert me.


Thank you,

Gibs

 

[Trebor76]

Hi Gibs,

Based on what Peter and Mike have already posted (plus a little input from me), try this while on the tab in question:

Sub PhNumCheck()

    Dim rCell As Range, _
        rRange As Range
    Dim sErrAddress As String
        
    Set rRange = Range("T1:T" & Range("T" & Rows.Count).End(xlUp).Row)
    
    Application.ScreenUpdating = False
    
    For Each rCell In rRange
    
        If rCell Like "##########" = False Then
            If sErrAddress = "" Then
                sErrAddress = rCell.Address
            Else
                sErrAddress = sErrAddress & vbNewLine & rCell.Address
            End If
        End If
        
    Next rCell
    
    Application.ScreenUpdating = True
    
    If sErrAddress = "" Then
        MsgBox "No format errors were detected.", vbInformation, "Phone Number Format Editor"
    Else
        MsgBox "The following cell addresses contain invalid phone number structures:" & vbNewLine & sErrAddress, vbExclamation, "Phone Number Format Editor"
    End If

End Sub

HTH

Robert

 

[mikerickson]

Try this sub

Sub test()
    Dim oneCell As Range
    With ThisWorkbook.Sheets("Sheet1").Range("T:T")
        For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            If Not (oneCell.Value = vbNullString Or oneCell.Text Like "##########") Then
                MsgBox oneCell.Address & " is bad."
            End If
        Next oneCell
    End With
End Sub