-
Excel 2003
Hello all,
just looking for some quick help, im not to great with excel so was rather happy when I got a search button/ box working so that if and when a person enters a word in the search box and clicks search, it searches the current sheet for matching words. This works fine.
However, if it doesnt find a matching word, it throws up a debug error at the moment. I would like to, if possible to make it, so that if a word isnt found, it displays a message rather than a debug error.
A message saying something like "The word you searched for was not found within the document", basically just somewhere I can put a message.
Can anyone help please?
The quick the better :)
-
Re: Excel 2003
What's the code you use to do the search?
-
Re: Excel 2003
Without knowing the actual code you've already written, it's hard to tell what is causing your error, so it's hard to suggest a solution!
I'm assuming you've written the search in VBA? If not, ignore all of the following!
The easiest way would be to write an error handler and call it by adding "On Error Goto ..." at the beginning of your script. This would simply run a particular bit of code any time an error occurred, regardless of the reason for that error. Search for "On Error Goto" in the Excel VBA help and you should be able to find some samples that you can crib - erm, I mean, learn from ;)
Better practice, however, would be to check the error that's being returned, work out why you're getting that error, and deal with it in the code. My best guess would be that you're trying to do something with a variable that has been set to "Nothing", which will generally throw errors left, right and centre :D So work out what's causing it, and add a check for Nothing to your code!
-
Re: Excel 2003
Code:
Dim fixsearch As String
Private Sub Search_Click()
fixsearch = SearchBox.Value
Sheets("Knowledgebase").Select
Cells.Find(fixsearch, after:=ActiveCell).Activate
End Sub
Private Sub SearchBox_Change()
End Sub
Would provide more information, but on a mission to beat the boss xD
-
Re: Excel 2003
Code:
Dim fixsearch As String
Private Sub Search_Click()
fixsearch = SearchBox.Value
Sheets("Knowledgebase").Select
on error goto notfound:
Cells.Find(fixsearch, after:=ActiveCell).Activate
Exit Sub
notfound:
MsgBox("Sorry, nothing matched your search")
End Sub
Private Sub SearchBox_Change()
End Sub
Try that
-
Re: Excel 2003
Cheers, that worked. This may be going a bit far and probably wont be a 2 second job, but is there a way of making it, so the message box displays the word searched for, so using your example above.
"Sorry, your search for "boobies" returned no results. Please try again"
?
-
Re: Excel 2003
Code:
Dim fixsearch As String
Private Sub Search_Click()
fixsearch = SearchBox.Value
Sheets("Knowledgebase").Select
on error goto notfound:
Cells.Find(fixsearch, after:=ActiveCell).Activate
Exit Sub
notfound:
MsgBox("Sorry, your search for " & fixsearch & " returned no results. Please try again")
End Sub
Private Sub SearchBox_Change()
End Sub
I believe.
-
Re: Excel 2003
Quote:
Originally Posted by
Repressor
Code:
Dim fixsearch As String
Private Sub Search_Click()
fixsearch = SearchBox.Value
Sheets("Knowledgebase").Select
on error goto notfound:
Cells.Find(fixsearch, after:=ActiveCell).Activate
Exit Sub
notfound:
MsgBox("Sorry, your search for """ & fixsearch & """ returned no results. Please try again")
End Sub
Private Sub SearchBox_Change()
End Sub
I believe.
Made a small change to the MsgBox line to give you the search string in quotes
-
Re: Excel 2003
Cheers to both, working fine now.
Just got to get on with actually finishing it lol!
-
Re: Excel 2003
Me, back again looking for more help.
I am trying to get a formula to read one column of data and for example look for ac42 and then look at another column for yes.
I think this has to be done as an Array formula, but I have no clue how.
Anyone able to help? Please?
-
Re: Excel 2003
What do you want it to do when it finds the "yes"?
-
Re: Excel 2003
Add one.
So basically it will find ac42 and then when it finds a yes it will increment the value in a cell by 1.
If have been trying and keep getting either VALUE or NAME errors..and like I have said, im not great with Excel so im learning as I go.
Cheers,
-
Re: Excel 2003
I've used an array formula like this before:
Code:
=AVERAGE(IF($C$2:$C$14="B",D$2:D$14))
Whereby if a cell in column C has a B in it, it takes the corresponding value from the D2 : D14 array and then takes the overall average of those values. I've had a quick stab at yours, but need a bit more time... I doubt I'm any better than you with Excel, but I have dabbled with arrays before. Will have a look tomorrow again if nobody comes in before me :p
-
Re: Excel 2003
Hahah cheers...i forgot to mention, the area im pulling/ looking at the information on is on seperate sheet but in the same work book
-
Re: Excel 2003
What is the formula you're using at present - the ones you're having difficulties with? I've got a couple of rough ideas but they are far from ideal.
-
Re: Excel 2003
Hmm i gave up on the complicated formula, it was =sumif(*crap*) etc.
Ive got it working in a dirty coding way, in one sheet i have it counting in a column per person, these columns are hidden, then on the other sheet it totals them up.
I have it counting, so im not in too much rush for it to work properly and "cleanly" so to speak.
but cheers :)