?
Solved

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

Posted on 2007-07-31
5
Medium Priority
?
1,130 Views
Last Modified: 2013-12-26
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
0
Comment
Question by:brettanderson77
  • 3
  • 2
5 Comments
 
LVL 38

Expert Comment

by:jeverist
ID: 19601438
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
 

Author Comment

by:brettanderson77
ID: 19602037
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
 

Author Comment

by:brettanderson77
ID: 19602046
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
 
LVL 38

Accepted Solution

by:
jeverist earned 2000 total points
ID: 19602207
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
 

Author Comment

by:brettanderson77
ID: 19607176
Great.... all good, nice work, thanks Jim.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question