Excel 2003

[Disturbedguy]

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

[Steve]

What's the code you use to do the search?

[scaryjim]

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 So work out what's causing it, and add a check for Nothing to your code!

[Disturbedguy]

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

[Flash477]

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

[Disturbedguy]

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"

?

[Repressor]

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.

[Flash477]

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

[Disturbedguy]

Cheers to both, working fine now.

Just got to get on with actually finishing it lol!

[Disturbedguy]

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?

[jim]

What do you want it to do when it finds the "yes"?

[Disturbedguy]

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,

[jim]

I've used an array formula like this before:

=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

[Disturbedguy]

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

[jim]

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.

[Disturbedguy]

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