VBA - Excel FindNext Input box

[Poudda]

Hi there,

I'm trying to set up a "find / next / replace" input box, but I have no clue how to make vb do this. Currently my code has a three step process. I have an input box that asks for a variable to find. Then when it's found, it puts it all the information on a given row into a spreadsheet. This would work just fine if there was only one row with the requested info. This is the code I have for the find function:

[CODE=vb]LookFor = InputBox("what is the client name?", "Search")
If LookFor = "" Then
End
End If

Cells.Find(What :=LookFor, After:=ActiveCe ll, LookIn:=xlFormu las, LookAt _
:=xlPart, SearchOrder:=xl ByRows, SearchDirection :=xlNext, MatchCase:= _
False, SearchFormat:=F alse).Activate[/CODE]

Of course I got this from using a macro. I can even add a line for a find next, but I don't have the correct input box to make this work.

I'd appreciate the help!

[Poudda]

Okay, I've made it work much better, but now if the item I'm looking for is not in my search request, the program stops working. Here is the new abbreviated code:


[CODE=vb]LookFor = InputBox("what is the client name?", "Search")
If LookFor = "" Then
End
End If

Cells.Find(What :=LookFor, After:=ActiveCe ll, LookIn:=xlFormu las, LookAt _
:=xlPart, SearchOrder:=xl ByRows, SearchDirection :=xlNext, MatchCase:= _
False, SearchFormat:=F alse).Activate

y = ActiveCell.Row


'bunch of code to take values from spreadsheet and put them into a form...


Do Until z = vbYes
z = MsgBox("Is this the correct client?", vbYesNoCancel)
If z = vbNo Then
Cells.FindNext( After:=ActiveCe ll).Activate
y = ActiveCell.Row
'bunch of code to take values from spreadsheet and put them into a form...
End If

If z = vbCancel Then
frmClient.txtRo ws.Text = fnRow
fnScrew
End If

If z = vbYes Then
frmClient.txtNa me.SetFocus
End If
Loop[/CODE]

[kadghar]

it wont work because the loop will continue forever, since is until you say that's the client you're looking for.

just have in mind what to do in case the user answers always no, or you just dont find the name.

Good Luck

[Poudda]

Actually, selecting "No" works just the way I want it to, it finds the next entry and adds the info from the row info to the form and then prompts the user with a "Yes/No/Cancel" question asking if it's the right client. This only loops for as long as the user chooses for it to loop.

I know my programming isn't as clean as it could be, however this program only appears to break down if the name I enter (which becomes LookFor) in the Cells.Find command isn't in the data list.

The error message I get when I enter a name that isn't there is: "Object variable or With block not set." Of course, I don't know what this means. I've seen the command "With" in some macros I've done, but I've never had to program one and am not sure how this is done within the Cells.Find line.

What I want the program to do if I enter a client name that isn't in the speadsheet, is to let me know that, "The client was not found, please enter another name." or "No client by that name found." And then clear the form and activate the cell at the bottom of the spreadseet (this part already in another module).

Thanks for your response, I hope this describes more clearly the bug I'm trying to fix.

[kadghar]

hi!!

I see the problem, you are always asking the code to activate the cells.find

what you must do is first check that the cells.find is different from nothing.
If the search is nothing, then put the msgbox ("sorry bla bla bal") else put the other procedure with a loop, (in this case it seemed easier to me to put a flag and reference it)

Hope this helps:

[CODE=vb]lookfor = InputBox("what is the client name?", "Search")
If lookfor = "" Then
End
End If

Flag1:
If Not Cells.Find(What :=lookfor, After:=ActiveCe ll, LookIn:=xlFormu las, LookAt _
:=xlPart, SearchOrder:=xl ByRows, SearchDirection :=xlNext, MatchCase:= _
False, SearchFormat:=F alse) Is Nothing Then

Cells.Find(What :=lookfor, After:=ActiveCe ll, LookIn:=xlFormu las, LookAt _
:=xlPart, SearchOrder:=xl ByRows, SearchDirection :=xlNext, MatchCase:= _
False, SearchFormat:=F alse).Activate

If MsgBox("Is this the correct client?", vbYesNoCancel) <> vbYes Then
GoTo Flag1
End If
Else
MsgBox ("The client is not in the list")
End If[/CODE]

[kadghar]

oh, and you can replace all that macro stuf with something like:

if not cells.find(lookfor) is nothing then
cels.find(lookfor).activate

or something like that

[Poudda]

Thank you a billion times over.

This solves the problem. I had no idea that goto commands even existed any more (last time I used one of these was on my commodore 64, back when we had to have a number for every line of code!).

The If Not.... Is Nothing Then command was also key to fix the problem.

Cheers!

[kadghar]

Thank you a billion times over.

This solves the problem. I had no idea that goto commands even existed any more (last time I used one of these was on my commodore 64, back when we had to have a number for every line of code!).

The If Not.... Is Nothing Then command was also key to fix the problem.

Cheers!

I'm glad it was useful

Kad

[Proaccesspro]

I'm glad it was useful

Kad

Could this be used in Access??? I'm trying to do something very similar via a search and replace functrion....

[kadghar]

Could this be used in Access??? I'm trying to do something very similar via a search and replace functrion....

yeap, just remember that you're not using cells in acces...

I cant tell you exactly how is done since i havnt got acces at the works pc, but give it a try and if you have some trouble i'll check it out latter at home.

[Proaccesspro]

yeap, just remember that you're not using cells in acces...

I cant tell you exactly how is done since i havnt got acces at the works pc, but give it a try and if you have some trouble i'll check it out latter at home.

Can you position where on the screen the search box appears??

[Killer42]

...I had no idea that goto commands even existed any more

Sure it exists, but the use of Goto still represents (usually) lazy coding. With proper control structures, GoTo should generally not be required.