Create a search database with return results to another cell.

[akshay0880]

Hey Guys,

I am trying to build sort of a database with a search in it.

So, what i am trying to do is:
When user enters a text in C7 and hit the button, it searches the text (exact match) in Sheet2(called Data) then displays it in Cell C19.
Attached screenshots for more clarity.
Thanks a lot in advance

 

[Wookiee]

Thank you for the screen shots, but can you please clarify the nature of the data, because to read your message it seems from the Search sheet you want to enter text in C7, match it on the Data sheet, but then display the same text that was entered in C19? I'm just unsure of the point of the matching function.

Is this a lookup situation, like the item to match is in Column C with the return value being in Column D? Because if that is the case, I would suggest merely using a lookup formula in Cell C19, like:

=XLOOKUP($C$7,Data!C:C,Data!D:D,"",0)

and you could format the cell so that it doesn't display 0 values for non-matches.

 

[akshay0880]

Hello, Thank you for replying. I found out a code that is currently working.

Sub Find()
Dim rng As Range
Dim Errors As String
Dim rownumber As Long

Errors = Sheet1.Cells(7, 3)


Set rng = Sheet2.Columns("A:A").Find(What:=Errors, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    rownumber = rng.Row
    Sheet1.Cells(19, 3).Value = Sheet2.Cells(rownumber, 2).Value

End Sub

I do have one more thing to add and not sure how to do this, its basically a message box.
so if I am typing something that is not in Sheet2, then i want it to say something like "not found'.
Right now if I do so, I am getting a 'run-time error '91': Object variable or With block variable not set'

Can you help please?

 

[Wookiee]

Hello, Thank you for replying. I found out a code that is currently working.

I do have one more thing to add and not sure how to do this, its basically a message box.
so if I am typing something that is not in Sheet2, then i want it to say something like "not found'.
Right now if I do so, I am getting a 'run-time error '91': Object variable or With block variable not set'

Can you help please?

The reason you get the error is because the macro is trying to extract the row number of something it cannot find.

I think that if you have to use a macro, you should use a LOOKUP formula (either VLOOKUP or XLOOKUP). Please try this macro:

Sub LookItUp()


With Sheets("Search").Range("C19")

  'Comment Out Whichever Formula You Prefer
  .FormulaR1C1 = "=IFERROR(VLOOKUP(R7C3,Data!C1:C2,2,0),""Not found"")"
  
  .FormulaR1C1 = "=XLOOKUP(R7C3,Data!C1,Data!C2,""Not found"",0)"

End With


End Sub