Question

Create an address book. Given a list of people addresses, create a procedure that allows a user to search for a particular name and returns the address from the table. (Refer to worksheet "14.23") Search for the name using the match function and the Application object. Name the range that contains the names in the address table. Then use the Offset property (Cells property) to gather the corresponding information. You may need to name another range to make this easier. Present this information to the user in a Message Box as shown below:

Answer 1

Answer: 'method definition Sub sample () 'variable decalratiorn Dim a As Range Dim b As Range Dim c As String Dim arr() As String Dim str As String c = InputBox ("Please enter the person name ") input is not enter then exit the program If Len (Trim(c)) 0 Then Exit Sub 'set the active sheet With Intersect (ActiveSheet.UsedRange, ActiveSheet.columns ("A") ) "set the autofilter .AutoFilter 1, c error checking On Error Resume Next

set the offset Set a .Offset (1) .Resize (.Rows.Count 1) .SpecialCells (xlCel1TypeVisible) 'if error came go to 0 On Error GoTo 0 end the autofilter .AutoFilter 'end the function End With If a Is Nothing Then 'display the message box MsgBox c & "could not be found in the address." Else For Each b In a.Cells str =b. Worksheet. Cells (b. Roy, "B"). Text arr Split (str, ) Msgox b & vbNewLine & arr (0) & vbNewLine & arr (1) & vbNewLine & arr (2) Next b End If

End Sub Output: Excel sheet: Bookl Microsoft Excel (Product Activation Fail 1回 File Home Insert Page Layout Formulas Data Review View Team a, calibri 표'-11.毒 Genera% , Fondation alblematting, ra Delete. Σ,知 Format as Table ▼ ja Delete- Paste Sort & Find & ormatSelect 0 .00 Cell Styles Clipboard Font Alignment Number Styles Cells Editing 86 Address 1 Name 2 C.Devis 534,Devisver CO,43535 3 W.John 213,Shenoy street,7674 4 A.William 12,Browny Street,65459 7 10 l<< 丶 丶1 Sheet1, Sheet2' Sheet3. Ready 100%

Input message box: Microsoft Excel Please enter the person name OK Cancel W John Output message box: Microsoft Excel W John 213 Shenoy street 7674 ок

Copyable code:

'method definition

Sub sample()

   

    'variable decalration

    Dim a As Range

    Dim b As Range

    Dim c As String

    Dim arr() As String

    Dim str As String

    c = InputBox("Please enter the person name")

   

    'input is not enter then exit the program

    If Len(Trim(c)) = 0 Then Exit Sub

    'set the active sheet

    With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("A"))

        'set the autofilter

        .AutoFilter 1, c

        'error checking

        On Error Resume Next

        'set the offset

        Set a = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

        'if error came go to 0

        On Error GoTo 0

        'end the autofilter

        .AutoFilter

    'end the function

    End With

  

    If a Is Nothing Then

        'display the message box

        MsgBox c & " could not be found in the address."

    Else

        For Each b In a.Cells

            str = b.Worksheet.Cells(b.Row, "B").Text

            arr = Split(str, ",")

            MsgBox b & vbNewLine & arr(0) & vbNewLine & arr(1) & vbNewLine & arr(2)

        Next b

    End If

End Sub