Option Explicit

Sub all_final()
On Error Resume Next
'-----------------------------------------------------------------------------------------
' Variable Declaration
Dim Cont_ISO_Code As String, Cont_cont_code As String, Cont_area_code As String
Dim Cont_res_cont_code As String, Cont_res_area_code As String
Dim Cli_ISO_Code As String, Cli_cont_code As String, Cli_area_code As String
Dim Cli_res_cont_code As String, Cli_res_area_code As String
Dim Prog_ISO_Code As String, Prog_cont_code As String, Prog_area_code As String
Dim Prog_res_cont_code As String, Prog_res_area_code As String
Dim m As Long, ctr As Long
Dim Cont_phone_result As String, Cli_phone_result As String, Prog_phone_result As String
Dim f_flag As Boolean
Dim Color_id As Integer

Dim fslash_pos As Integer, char_pos As Integer, comma_pos As Integer
'----------------------------------------------------------------------------------------
' Constants
'Note: Please initialise before every run of macro
Const ph_col = "G"                        'Phone Result column number
Const Cont_col = "M"                      'Contact country column number
Const Cli_col = "P"                       'Client Country column number
Const Code_Col = "I"                      'Separated country code column number
Const Area_Col = "J"                      'Separated area code column number
Const Num_Col = "K"                       'Separated number column number
Const RECNUM = 9413                        'Total records in Main Sheet
Const CEND = 76                           'Total records in Codes sheet
Const Prog_Col = "R"                      'Program country column number or default
Const Status_Col = "A"                     'Temp Column
'----------------------------------------------------------------------------------------

Color_id = 38
For m = 3 To RECNUM
    Cont_phone_result = Trim(Main.Range(ph_col & m).Value)
    Cont_phone_result = Replace(Cont_phone_result, "+", "")
    Cont_phone_result = Replace(Cont_phone_result, " ", "")
    Cont_phone_result = Replace(Cont_phone_result, "(", "")
    Cont_phone_result = Replace(Cont_phone_result, ")", "")
    Cont_phone_result = Replace(Cont_phone_result, "-", "")
    Cont_phone_result = Replace(Cont_phone_result, ".", "")
    Cont_phone_result = Replace(Cont_phone_result, "[", "")
    Cont_phone_result = Replace(Cont_phone_result, "]", "")
    Cont_phone_result = Replace(Cont_phone_result, "=", "")
    Cont_phone_result = Replace(Cont_phone_result, "_", "")
    If Mid(Cont_phone_result, 1, 3) = "011" Then
        Cont_phone_result = Mid(Cont_phone_result, 4)
    End If
    Cont_phone_result = CStr(Val(Cont_phone_result))
    fslash_pos = InStr(1, Cont_phone_result, "/")
    char_pos = Cont_phone_result Like "[a-z]"
    comma_pos = InStr(1, Cont_phone_result, ",")
    If fslash_pos > 1 Then
        Cont_phone_result = Mid(Cont_phone_result, 1, fslash_pos - 1)
    End If
    If comma_pos > 1 Then
        Cont_phone_result = Mid(Cont_phone_result, 1, comma_pos - 1)
    End If
    If IsNumeric(Cont_phone_result) Then
        Cli_phone_result = Cont_phone_result
        Prog_phone_result = Cont_phone_result
        Cont_ISO_Code = Trim(Main.Range(Cont_col & m).Value)
        Cli_ISO_Code = Trim(Main.Range(Cli_col & m).Value)
        Prog_ISO_Code = Trim(Main.Range(Prog_Col & m).Value) '"US"
        Cont_cont_code = Trim(Worksheets(Cont_ISO_Code).Range("B2"))
        Cont_res_cont_code = Trim(Mid(Cont_phone_result, 1, Len(Cont_cont_code)))
        If Cont_res_cont_code = Cont_cont_code Then
            Cont_phone_result = Right(Cont_phone_result, Len(Cont_phone_result) - Len(Cont_cont_code))
            Color_id = 35
        End If
        ctr = 2
        f_flag = False
        Do While (1)
            Cont_area_code = ""
            Cont_area_code = Trim(Worksheets(Cont_ISO_Code).Range("D" & ctr))
            If Cont_area_code = "" Then
                Exit Do
            End If
            Cont_res_area_code = Trim(Mid(CStr(Val(Cont_phone_result)), 1, Len(Cont_area_code)))
            If Val(Cont_area_code) = Val(Cont_res_area_code) Then
                Cont_phone_result = Right(Cont_phone_result, Len(Cont_phone_result) - Len(Cont_area_code))
                Main.Range(Code_Col & m).Value = Cont_cont_code
                Main.Range(Area_Col & m).Value = Cont_area_code
                Main.Range(Num_Col & m).Value = Cont_phone_result
                f_flag = True
                Color_id = 35
                Exit Do
            End If
            ctr = ctr + 1
        Loop
        If (ctr = 2) And (f_flag = False) Then
            Main.Range(Code_Col & m).Value = Cont_cont_code
            Main.Range(Num_Col & m).Value = Cont_phone_result
            Color_id = 35
            f_flag = True
        End If
        If Trim(Cont_ISO_Code) = "" Then
            f_flag = False
        End If
        '---------------------------------------------------------------------------------------
        'End of check and separation for contact country
        '---------------------------------------------------------------------------------------
        
        If f_flag = True Then
            If (Len(Cont_area_code) > 3) Or (Len(Cont_phone_result) > 8) Or (Len(Cont_phone_result) <= 6) Then
                Color_id = 36
            End If
            Range("A" & m & ":P" & m).Select
            Selection.Interior.ColorIndex = Color_id
        Else
            If Cont_ISO_Code <> Cli_ISO_Code Then
            '---------------------------------------------------------------------------------------
            'Start of check and separation for Client country
            '---------------------------------------------------------------------------------------
                Cli_cont_code = Trim(Worksheets(Cli_ISO_Code).Range("B2"))
                Cli_res_cont_code = Trim(Mid(Cli_phone_result, 1, Len(Cli_cont_code)))
                If Cli_res_cont_code = Cli_cont_code Then
                    Cli_phone_result = Right(Cli_phone_result, Len(Cli_phone_result) - Len(Cli_cont_code))
                    Color_id = 35
                End If
                ctr = 2
                f_flag = False
                Do While (1)
                    Cli_area_code = ""
                    Cli_area_code = Trim(Worksheets(Cli_ISO_Code).Range("D" & ctr))
                    If Cli_area_code = "" Then
                        
                        Exit Do
                    End If
                    Cli_res_area_code = Trim(Mid(CStr(Val(Cli_phone_result)), 1, Len(Cli_area_code)))
                    If Val(Cli_area_code) = Val(Cli_res_area_code) Then
                        Cli_phone_result = Right(CStr(Val(Cli_phone_result)), Len(Cli_phone_result) - Len(Cli_area_code))
                        Main.Range(Code_Col & m).Value = Cli_cont_code
                        Main.Range(Area_Col & m).Value = Cli_area_code
                        Main.Range(Num_Col & m).Value = Cli_phone_result
                        f_flag = True
                        Color_id = 35
                        Exit Do
                    End If
                    ctr = ctr + 1
                Loop
                If (ctr = 2) And (f_flag = False) Then
                    Main.Range(Code_Col & m).Value = Cli_cont_code
                    Main.Range(Num_Col & m).Value = Cli_phone_result
                    Color_id = 35
                    f_flag = True
                End If
                If f_flag = True Then
                    If (Len(Cli_area_code) > 3) Or (Len(Cli_phone_result) > 8) Or (Len(Cli_phone_result) <= 6) Then
                        Color_id = 36
                    End If
                    Range("A" & m & ":P" & m).Select
                    Selection.Interior.ColorIndex = Color_id
                    '--------------------------------------------------------------------------
                    'End of check and separation for contact country
                    '--------------------------------------------------------------------------
                Else
                    '---------------------------------------------------------------------------------------
                    'Start of check and separation for Prog country
                    '---------------------------------------------------------------------------------------
                    Prog_cont_code = Trim(Worksheets(Prog_ISO_Code).Range("B2"))
                    Prog_res_cont_code = Trim(Mid(CStr(Val(Prog_phone_result)), 1, Len(Prog_cont_code)))
                    If Prog_res_cont_code = Prog_cont_code Then
                        Prog_phone_result = Right(Prog_phone_result, Len(Prog_phone_result) - Len(Prog_cont_code))
                        Color_id = 36
                    End If
                    ctr = 2
                    f_flag = False
                    Do While (1)
                        Prog_area_code = ""
                        Prog_area_code = Trim(Worksheets(Prog_ISO_Code).Range("D" & ctr))
                        If Prog_area_code = "" Then
                            Exit Do
                        End If
                        Prog_res_area_code = Trim(Mid(CStr(Val(Prog_phone_result)), 1, Len(Prog_area_code)))
                        If Val(Prog_area_code) = Val(Prog_res_area_code) Then
                            Prog_phone_result = Right(Prog_phone_result, Len(Prog_phone_result) - Len(Prog_area_code))
                            Main.Range(Code_Col & m).Value = Prog_cont_code
                            Main.Range(Area_Col & m).Value = Prog_area_code
                            Main.Range(Num_Col & m).Value = Prog_phone_result
                            f_flag = True
                            Color_id = 36
                            Exit Do
                        End If
                        ctr = ctr + 1
                    Loop
                    If (ctr = 2) And (f_flag = False) Then
                        Main.Range(Code_Col & m).Value = Prog_cont_code
                        Main.Range(Num_Col & m).Value = Prog_phone_result
                        Color_id = 36
                        f_flag = True
                    End If
                    If f_flag = True Then
                        If (Len(Prog_area_code) > 3) Or (Len(Prog_phone_result) > 8) Or (Len(Prog_phone_result) <= 6) Then
                            Color_id = 36
                        End If
                        Range("A" & m & ":P" & m).Select
                        Selection.Interior.ColorIndex = Color_id
                    Else
                        Color_id = 38
                        Range("A" & m & ":P" & m).Select
                        Selection.Interior.ColorIndex = Color_id
                    End If
                    '--------------------------------------------------------------------------
                    'End of check and separation for Prog country
                    '--------------------------------------------------------------------------
                End If
            ElseIf Cont_ISO_Code <> Prog_ISO_Code Then
                '---------------------------------------------------------------------------------------
                'Start of check and separation for Prog country
                '---------------------------------------------------------------------------------------
                Prog_cont_code = Trim(Worksheets(Prog_ISO_Code).Range("B2"))
                Prog_res_cont_code = Trim(Mid(Prog_phone_result, 1, Len(Prog_cont_code)))
                If Prog_res_cont_code = Prog_cont_code Then
                    Prog_phone_result = Right(Prog_phone_result, Len(Prog_phone_result) - Len(Prog_cont_code))
                    Color_id = 36
                End If
                ctr = 2
                f_flag = False
                Do While (1)
                    Prog_area_code = ""
                    Prog_area_code = Trim(Worksheets(Prog_ISO_Code).Range("D" & ctr))
                    If Prog_area_code = "" Then
                        
                        Exit Do
                    End If
                    Prog_res_area_code = Trim(Mid(Prog_phone_result, 1, Len(Prog_area_code)))
                    If Val(Prog_area_code) = Val(Prog_res_area_code) Then
                        Prog_phone_result = Right(Prog_phone_result, Len(Prog_phone_result) - Len(Prog_area_code))
                        Main.Range(Code_Col & m).Value = Prog_cont_code
                        Main.Range(Area_Col & m).Value = Prog_area_code
                        Main.Range(Num_Col & m).Value = Prog_phone_result
                        f_flag = True
                        Color_id = 36
                        Exit Do
                    End If
                    ctr = ctr + 1
                Loop
                If (ctr = 2) And (f_flag = False) Then
                    Main.Range(Code_Col & m).Value = Prog_cont_code
                    Main.Range(Num_Col & m).Value = Prog_phone_result
                    Color_id = 36
                    f_flag = True
                End If
                If f_flag = True Then
                    If (Len(Prog_area_code) > 3) Or (Len(Prog_phone_result) > 8) Or (Len(Prog_phone_result) <= 6) Then
                        Color_id = 36
                    End If
                    Range("A" & m & ":P" & m).Select
                    Selection.Interior.ColorIndex = Color_id
                Else
                    Color_id = 38
                    Range("A" & m & ":P" & m).Select
                    Selection.Interior.ColorIndex = Color_id
                End If
                    '--------------------------------------------------------------------------
                    'End of check and separation for Prog country
                    '--------------------------------------------------------------------------
            End If
        End If
        Select Case Color_id
            Case 35: Main.Range(Status_Col & m).Value = 1
            Case 36: Main.Range(Status_Col & m).Value = 2
            Case 38: Main.Range(Status_Col & m).Value = 3
        End Select
    End If
Next

'-------------------------------------------------------------------------------------------
'Start of scan 2
'-------------------------------------------------------------------------------------------


End Sub

Sub manual()
Dim ph As String
Dim startpos As Integer, endpos As Integer
Dim s_code As String
Dim ctr As Integer
Dim f_flag As Boolean
Dim acode As String, sheet_name As String, pNumber As String
ph = Main.Range("G" & ActiveCell.Row)
ph = Replace(ph, "+", "")
ph = Replace(ph, " ", "")
ph = Replace(ph, "(", "")
ph = Replace(ph, ")", "")
ph = Replace(ph, "-", "")
ph = Replace(ph, ".", "")
ph = Replace(ph, "[", "")
ph = Replace(ph, "]", "")
ph = Replace(ph, "=", "")
ph = Replace(ph, "_", "")
ph = CStr(Val(ph))
Main.Range("I" & ActiveCell.Row) = InputBox("Enter country code")
f_flag = False
Do While (MsgBox("Search area code", vbYesNo) = vbYes)
    acode = InputBox("Enter area code")
    sheet_name = InputBox("Enter sheet Name")
    ctr = 2
    f_flag = False
    Do While (1)
        s_code = ""
        s_code = Worksheets(sheet_name).Range("D" & ctr)
         If s_code = "" Then
            Exit Do
          End If
        If Val(s_code) = Val(acode) Then
            f_flag = True
            Exit Do
        End If
        ctr = ctr + 1
    Loop
    If f_flag = True Then
        Exit Do
    End If
Loop
If f_flag = False Then
    acode = InputBox("Enter ur area code")
End If
Main.Range("J" & ActiveCell.Row) = acode

startpos = InStr(1, ph, InputBox("Enter start" & " : " & ph, , acode))
If MsgBox("Is there a terminator", vbYesNo) = vbYes Then
    endpos = InStr(1, ph, InputBox("Enter terminator"))
    pNumber = Mid(ph, startpos + Len(startpos), endpos - 1)
Else
    pNumber = Mid(ph, startpos + Len(acode))
End If
Main.Range("K" & ActiveCell.Row) = pNumber


If Len(pNumber) <> InputBox("Enter Length to check") Then
    Main.Range("A" & ActiveCell.Row) = 2
     Main.Range("B" & ActiveCell.Row) = InputBox("Enter Comment")
'    MAin.Range("A" & ActiveCell.Row & ":T" & ActiveCell.Row).Interior.ColorIndex = 6
Else
    Main.Range("A" & ActiveCell.Row) = 1
'    MAin.Range("A" & ActiveCell.Row & ":T" & ActiveCell.Row).Interior.ColorIndex = 4
End If

End Sub



Sub getnum()
Dim ph As String
Dim ctr As Integer
For ctr = 3 To 9505
ph = Main.Range("G" & ctr)
ph = Replace(ph, "+", "")
ph = Replace(ph, " ", "")
ph = Replace(ph, "(", "")
ph = Replace(ph, ")", "")
ph = Replace(ph, "-", "")
ph = Replace(ph, ".", "")
ph = Replace(ph, "[", "")
ph = Replace(ph, "]", "")
ph = Replace(ph, "=", "")
ph = Replace(ph, "_", "")
ph = CStr(Val(ph))
Main.Range("U" & ctr) = CStr(ph)
Next
End Sub
.....................................................................................................................................................

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/20/2002 by kanbay
'
' Keyboard Shortcut: Ctrl+t
'
Dim ph As String
For i = 2 To 13230
    ph = Sheet1.Range("F" & i)
    ph = Replace(ph, "(", "")
    ph = Replace(ph, ")", "")
    ph = Replace(ph, "[", "")
    ph = Replace(ph, "]", "")
    ph = Replace(ph, ".", "")
    ph = Replace(ph, "-", "")
    ph = Replace(ph, "_", "")
    ph = Replace(ph, ",", "")
    ph = Replace(ph, " ", "")
    ph = Replace(ph, "+", "")
    
    If (Len(ph) <= 7) Then
       'Sheet1.Range("o" & i) = "3"
       Sheet1.Range("A" & i) = 3
       Sheet1.Range("B" & i) = "Pattern Found - xxx-[x]"
    ElseIf ((Left(ph, 2) = "49") And (Len(ph) <= 10)) Then
        'Sheet1.Range("o" & i) = "3"
        Sheet1.Range("A" & i) = 3
        Sheet1.Range("B" & i) = "Pattern Found - 49-[x]"
    ElseIf ((Left(ph, 2) = "46") And (Len(ph) <= 11)) Then
        'Sheet1.Range("o" & i) = "3"
        Sheet1.Range("A" & i) = 3
        Sheet1.Range("B" & i) = "Pattern Found - 46-[x]"
    ElseIf ((Left(ph, 3) = "352") And (Len(ph) <= 12)) Then
        'Sheet1.Range("o" & i) = "3"
        Sheet1.Range("A" & i) = 3
        Sheet1.Range("B" & i) = "Pattern Found - 352-[x]"
    End If
    If Not ((IsNumeric(ph))) And (Sheet1.Range("o" & i) <> 3) Then
        'Sheet1.Range("o" & i) = "4"
        Sheet1.Range("A" & i) = 3
        Sheet1.Range("B" & i) = "Pattern Found - Non-Numeric Data"
    End If
Next
End Sub