VBA code in excel to search contents and go to or display corresponding results

Hi,

I have a table of data in excel.... first column (B) has acronyms in it.

I wish to have a cell above this to act like an input text box and have a search button which then executes a VBA search and either goes to the cell in row B which contains that term or idealy, would retrieve the meaning (text) from the corresponding cell in the next column and display/paste it in cell D4.

I wish to have a msg box appear if nothing is found.

I have drafted the following code yet have no idea how to pick up the contents of the cell based on the cell location (C4): (a button would call the following procedure)

Sub SearchAcro()
    Cells.Find(What:=Range("C4"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

'then an IF statement or something - IF found then go to cell or display result in cell D4, else msg box

End Sub

Cheers,
Brett
brettanderson77Asked:
Who is Participating?
 
jeveristConnect With a Mentor Commented:
Brett,

>  cant see why

Me neither.  It is searching every row in Column B for a partial match.  Are you it's not finding something?

>  code with the right cells

As long as the search is still in Column B and the Descriptions are in Column C then we should be OK.

>  Would you be able to assist?

Sure!  Try this revision:

Sub SearchAcro()
    Dim frg As Range
   
    Set frg = Nothing
    Set frg = Columns("B").Find(What:=Range("D4"), LookIn:=xlFormulas, LookAt:=xlPart)
   
    If Not frg Is Nothing Then
        If Len(Trim(frg.Offset(0, 1).Value)) = 0 Then
            MsgBox "No description has been entered."
        Else
            Range("E4") = frg.Offset(0, 1).Value
        End If
    Else
        MsgBox "Value: " & Range("D4").Value & " Not Found!"
    End If
End Sub

Jim
0
 
jeveristCommented:
Hi Brett,

Try this:

Sub SearchAcro()
    Dim frg As Range
    Set frg = Columns("B").Find(What:=Range("C4"), LookIn:=xlFormulas, LookAt:=xlPart)
   
    If Not frg Is Nothing Then
        Range("D4") = frg.Offset(0, 1)
    Else
        MsgBox "Value: " & Range("C4").Value & " not found!"
    End If
End Sub

Jim
0
 
brettanderson77Author Commented:
Hi Jim,

Thanks for that. It works other than the message box, cant see why.

Also, if the term is found but the corresponding cell across one column has no description in it, then a message box saying "No discription has been entered" would also be required.

Would you be able to assist?

Cheers,
Brett
0
 
brettanderson77Author Commented:
PS - here's the code with the right cells, i gave you the wrong ones:

Sub SearchAcro()
    Dim frg As Range
    Set frg = Columns("B").Find(What:=Range("D4"), LookIn:=xlFormulas, LookAt:=xlPart)
   
    If Not frg Is Nothing Then
        Range("E4") = frg.Offset(0, 1)
    Else
        MsgBox "Value: " & Range("D4").Value & " Not Found!"
    End If
End Sub
0
 
brettanderson77Author Commented:
Great.... all good, nice work, thanks Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.