EXCEL VBA PROGRAMMING Create an address book given the follo

EXCEL VBA PROGRAMMING

Create an address book given the following table where a user can search a name and a message box returns the address.

Search for the name using MATCH, then use OFFSET to gather the information.

Present in MSG BOX as

A. Name

123 ST

City, State

ZIP###

Solution

Private Sub Search_Click()
Dim Name As Variant
Dim Name1 As Variant
Dim Street As Variant
Dim City As Variant
Dim State As Variant
Dim Zip As Variant
Dim Var As Variant
Dim Var2 As Variant
Dim Var3 As Variant
Dim Var4 As Variant
Dim Var5 As Variant
Name = InputBox(\"Enter Name \", \"Search Name\")
For i = 2 To 104856 Step 1
Var = \"A\" & i
Name1 = Worksheets(\"Address_Book\").Range(Var).Value
If StrComp(Name, Name1, 1) = 0 Then
Var2 = \"B\" & i
Var3 = \"C\" & i
Var4 = \"D\" & i
Var5 = \"E\" & i
Street = Worksheets(\"Address_Book\").Range(Var2).Value
City = Worksheets(\"Address_Book\").Range(Var3).Value
State = Worksheets(\"Address_Book\").Range(Var4).Value
Zip = Worksheets(\"Address_Book\").Range(Var5).Value
MsgBox Name & vbNewLine & Street & vbNewLine & City & \",\" & State & vbNewLine & Zip
Exit For
ElseIf StrComp(Name1, \"\", 1) = 0 Then
MsgBox \"Didnt find\"
Exit For
End If
Next
End Sub