Excel/macro search

Advertisement


Question
QUESTION: I've been asked to put together a FAQ spreadsheet for work and I'm hoping to use a macro to keep it simple for the user. Is it possible for a macro to search a page from a word or 2 entered in a cell on page 1, then return the all the sentences in full where it finds these words? If possible, I'd like them to be linked so they can lead to the relevant answer. If so, what would it be as my macro skills are pretty much non existent? Thanks

ANSWER: It's a fairly simple macro

Sub tester()
Dim MyVar As String
Dim OutVar As Long
OutVar = 2
MyVar = Range("a1").Value
For Each cell In Range("C1:C200")
   If InStr(cell.Value, MyVar) > 0 Then
       ActiveSheet.Hyperlinks.Add Anchor:=Cells(OutVar, 1), Address:="", SubAddress:=ActiveSheet.Name & "!" & cell.Address, TextToDisplay:=cell.Value
       OutVar = OutVar + 1
   End If
Next
End Sub

this uses A1 as the cell to contain the text to search, and searches all cells in c1:C200 - amend those references as required and hopefully it will work for you.

---------- FOLLOW-UP ----------

QUESTION: Thanks for this Aidan, there are just a couple of additions I'm struggling to  put in. If the cell is empty, or if the word they're searching for doesn't give a result, how can I add a error message box that pops up to let them know? Also there are a number of different sheets is searches, how do you search through sheet 3, sheet 3 etc? Thanks again

ANSWER: The easiest way to do all sheets is something like

for each sht in activeworkbook.sheets
'to exlude one
if sht.name<>"The Excluded one's name" then

end if
next

if you have an empty cell returned, then

if range("A1").value="" then msgbox "No results found"

would do it - replace A1 with an appropriate cell reference!  In your case, OutVar (my variable) started at 2, you could check this as well

if outvar=2 then
'no results returned
msgbox "no results found"
end if

---------- FOLLOW-UP ----------

QUESTION: Thanks again but I'm a complete beginner when it comes to macro's. I've spent some time trying to substitute or insert these new lines but I keep getting error messages such as end but no if, next without for, etc. Would you be able to show me the full macro please? Thanks

Answer
The macro would be something like

Sub tester()
Dim MyVar As String
Dim OutVar As Long
OutVar = 2
MyVar = Range("a1").Value

for each sht in activeworkbook.sheets
'to exlude one
if sht.name<>"The Excluded one's name" then
For Each cell In sht.Range("C1:C200")
  If InStr(cell.Value, MyVar) > 0 Then
      ActiveSheet.Hyperlinks.Add Anchor:=Cells(OutVar, 1), Address:="", SubAddress:=sht.Name & "!" & cell.Address, TextToDisplay:=cell.Value
      OutVar = OutVar + 1
  End If
Next

end if
next


if outvar=2 then
'no results returned
msgbox "no results found"
end if


End Sub



I'm happy to look at this further for you if needed - if it helps (to enable you to share the workbook) my direct email is aidan.heritage@virgin.net
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.