Sam Coombes
asked on
Search Box
Can any one tell me why this code is not working. I want it to search my spreadsheet and display the data from select rows if the correct number is entered into the search field. Basically a basic search engine.
Private Sub CommandButton1_Click()
Row_Number = 1
Do
DoEvents
Row Number = Row_Number + 1
item_in_review = Sheets("Blueteq").Range("B " & Row_Number)
If item_in_review = TextBox1.Text Then
TextBox2.Text = Sheets("Blueteq").Range("O " & Row_Number)
TextBox2.Text = Sheets("Blueteq").Range("K " & Row_Number)
TextBox2.Text = Sheets("Blueteq").Range("L " & Row_Number)
End If
Loop Until item_in_review = ""
End Sub
Private Sub CommandButton1_Click()
Row_Number = 1
Do
DoEvents
Row Number = Row_Number + 1
item_in_review = Sheets("Blueteq").Range("B
If item_in_review = TextBox1.Text Then
TextBox2.Text = Sheets("Blueteq").Range("O
TextBox2.Text = Sheets("Blueteq").Range("K
TextBox2.Text = Sheets("Blueteq").Range("L
End If
Loop Until item_in_review = ""
End Sub
Add item_in_review = "" before the End If.
ASKER
Thank you I have now tried this but an occurs with the row number = 0
Says can't find project or library
Says can't find project or library
Can you attach your workbook?
In addition to what Martin mentioned,
1. There is no need for DoEvents in your loop. Looping rows is so fast there is no need to yield the processor for other tasks.
2. Row Number = Row_Number + 1 should be at the end of the loop, i.e. one line before the Loop Until line.
3. You are overwriting TextBox2 so at the end you will only see the data from the L row.
1. There is no need for DoEvents in your loop. Looping rows is so fast there is no need to yield the processor for other tasks.
2. Row Number = Row_Number + 1 should be at the end of the loop, i.e. one line before the Loop Until line.
3. You are overwriting TextBox2 so at the end you will only see the data from the L row.
Can't find project or library, usually means there is a missing reference in the file.
In the IDE goto the Tools menu, then select References, if any are marked "Missing", remove them.
Then Goto the Debug menu and click Compile VbaProject.
In the IDE goto the Tools menu, then select References, if any are marked "Missing", remove them.
Then Goto the Debug menu and click Compile VbaProject.
And you don't need to loop (and shouldn't because it a lot slower).
Private Sub CommandButton1_Click()
Dim rngFound As Range
With Sheets("Blueteq")
Set rngFound = .Columns("B:B").Find(What:=TextBox1.Text)
If Not rngFound = Nothing Then
TextBox2.Text = .Range("O" & rngFound.Row) & " " & _
.Range("K" & rngFound.Row) & " " & _
.Range("L" & rngFound.Row)
Else
MsgBox "Not found"
End If
End With
End Sub
ASKER
That's amazing thank you so much, but why does it now say there is an error with
'If Not rngFound = Nothing Then' highlighting an issue with the nothing statement
'If Not rngFound = Nothing Then' highlighting an issue with the nothing statement
Can you attach your workbook as it is now?
ASKER
Thank you I have attached the spreadsheet which looks a mess because I have removed all of the other sheets for governance reasons. Also there is no real data on the sheet you have. High-Cost-Drugs-Database-2015---201.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sam, did my previous pos resolve your question?
ASKER
Brilliant thank you so much.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015