Excel help please

[Colbaker]

Hi all,

Was wondering what you gyys would do in ecars for thus situation, basically I am looking at creating a kind of faq spreadsheet where there is a search box that will search the data page for the entered keyword and return the info on that keyword. Would it just be a normal vlookup or index match?

 

[Phonesis]

Hi all,

Was wondering what you gyys would do in ecars for thus situation, basically I am looking at creating a kind of faq spreadsheet where there is a search box that will search the data page for the entered keyword and return the info on that keyword. Would it just be a normal vlookup or index match?

You might want to consider some VBA code for something like this. Ever dabbled with it?

 

[joeyjojo]

Sounds like one of those things people do in Excel for which Excel isn't the right tool.

 

[Colbaker]

You might want to consider some VBA code for something like this. Ever dabbled with it?

I did wonder, never done any VBA, how easy is it to pick up?

 

[Grrrrr]

What you're asking would get very complicated, very quickly trying to use a formula. For example if you search something like "file" there might be a dozen hits. If you try to implement something to cycle through those hits you're into the realms of array formulae or VBA which are pretty advanced.

Your best bet is just using Excel's built in Find tool (Ctrl+F)

 

[Phonesis]

I did wonder, never done any VBA, how easy is it to pick up?

Not that easy to be honest. I've done some though and found a possibly relevant post on StackOverFlow about it.

What you need to do is save the excel file as a macro enabled one. Then in the sheet your data exists in right click on the tab and select view code. Now paste this in:

Sub PlayMacro()

  Dim Prompt As String
  Dim RetValue As String
  Dim Rng As Range
  Dim RowCrnt As Long
  Dim Info As String

  Prompt = ""

  With Sheets("Sheet1")

    ' This will loop forever unless a statement within
    ' the loop exits the Do.
    Do While True

      RetValue = InputBox(Prompt & "Give me a value to look for")
      'RetValue will be empty if you click cancel
      If RetValue = "" Then
        Exit Do
      End If

      ' I do not wish to active the cell containing the required value.
      ' I want to know where it is.
      Set Rng = .Columns("A:A").Find(What:=RetValue, After:=.Range("A1"), _
                LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

      If Rng Is Nothing Then
        ' The entered value could not be found
        Prompt = "I could not find """ & RetValue & """"
      Else
        ' The entered value was found
        RowCrnt = Rng.Row
        Info = Rng.Value
        
        Prompt = "I found """ & RetValue & """ on row " & RowCrnt & " with value " & Info
      End If
      Prompt = Prompt & vbLf
    Loop

  End With

End Sub

make "Sheet1" the name of your sheet ("Data" or whatever it is). Save and then when back on the sheet in excel create a button. You need to have Developer tab visible for this. Google how to enable it and assign a button to a macro (which will be the above code).