I have a macro that selects a range in column a and b, and searches for a string of characters - in the example below, it's searching for sales how do I alter this code so I don't have to change the coding, and also is there a way to handle if nothing is found ?

Application.Goto Reference:="R1C1:R55C2"
Selection.Find(What:="sales", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

dim strSearch as String
strSearch =InputBox("Enter the search term")



I believe this will work.

also bob recently had a post with a suggestion here, in regard to error handling on Find, may want to check it out it may help

Isaac,
that worked GREAT ! Thanks

I checked out that link as well

thanks again !!!!!!

Steve

hmm - I tried running the query from a button on the quick access toolbar, and it's not working !



if I run it from a code window, it works fine - very odd

Steve

If you use Selection in code called from a command button, there's a good chance that selection is actually the command button.

Thanks Norie. I have benefitted greatly from your advice about the use of Select and Selection, but sometimes I get lazy about mentioning it each time.

not sure I understand.



didn't work - you left off the .Activate but that wasn't the issue - it didn't run either way - and I didn't use the button

What happens if you change
After:=Range("A1:B55")

to

After:=Range("A1")

My mistake, it should be After:=Range("A1").

What I mean is that the command button is selected, so when you have Selection.Find, Selection is the command button.

Pretty sure you can't search a command button, or maybe you can.

The reason I left Activate off was because if the find doesn't work there's nothing to activate, so the code will fail.

The following If checks if the Find has worked and if it has move to the found range/cell using Application.Goto.

Norie,
Nope - didn't work

I'm not following this.

Why did you add Activate back in?

There's another mistake in the code though, it should be Application not Applcation.

PS Don't you have Intellisense here?

I took activate back out - and I fixed the typo - I should've used intellisense

Steve

Steve

No problem.

About the Selection thing, in Excel Selection could be anything, within reason, including a button.

So when you use Selection in the code it could be referring to the button, not the range you want to search.

I don't know how you can empirically test it but try playing about with TypeName(Selection).

Make any sense now.