Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Using VBA enter three letters of a word into a form or cell and have a msgbox show a list of words

    Is there a way to use VBA or a formula to search Column A by typing in the first 3 letter of a word and then have it show via a message box?

    It would additionally show the associated information found in Columns B,C,D,and E. There may be some blank cells.

    See the attached file for visual representation.

    I'm an Excel newbie trying to learn more each day.

    Thanks in advance for your help. Much appreciated.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-04-16 at 17:38.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Newbie,

    This should get you started:
    Code:
    Option Explicit
    
    Sub SiteSearch()
    
       Dim zSearchValue As String
       Dim zMsgList     As String
       Dim lCntr        As Long
       Dim lColCntr     As Long
       
       zSearchValue = InputBox("Enter first three letters of Site", _
                               "Search Parameter:")
                               
       If Trim(zSearchValue = "") Then Exit Sub
       
       lCntr = 2
       zSearchValue = UCase(zSearchValue)  '*** Make case insensitive! ***
       Do
         
         If UCase(Left(Cells(lCntr, 1).Value, 3)) = zSearchValue Then
           For lColCntr = 1 To 5
              zMsgList = zMsgList & Cells(lCntr, lColCntr).Value & vbTab
           Next lColCntr
              zMsgList = zMsgList & vbCrLf
         End If
         
         lCntr = lCntr + 1   '*** Increment Counter ***
         
       Loop Until Cells(lCntr, 1).Value = ""
       
       MsgBox "Here are the Matches:" & vbCrLf & vbCrLf & zMsgList, _
              vbOKOnly, "Site Matches:"
              
       
    End Sub      'SiteSearch()
    QFDPrompt.JPG

    QDFResults.JPG

    Test File: Quick Find Directory.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2015-04-16)

  4. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Put this code in the sheet module. See Quick Find Directory.xlsm
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim mv As String 'Range
    If Target.Address <> Range("f1").Address Then Exit Sub
    If Len(Target) >= 3 Then
    mv = Columns(1).Find(What:=Target, After:=Cells(1, 1), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=mv
    End If
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address <> Range("g1").Address Then Exit Sub
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    End Sub
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. The Following User Says Thank You to Supershoe For This Useful Post:

    Excelnewbie (2015-04-16)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Excel,

    Start entering letters in the search box and your list will be filtered as you type

    HTH,
    Maud

    Prior to search"
    excel1.png

    After Search:
    Excel2.png
    Attached Files Attached Files

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2015-04-16)

  8. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-thank you for everything. This is very helpful.

  9. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Supershoe-I appreciate your help.

  10. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maudibe-big thanks. I appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •