By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,666 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,666 IT Pros & Developers. It's quick & easy.

VBA - Excel FindNext Input box

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1. LookFor = InputBox("what is the client name?", "Search")
  2. If LookFor = "" Then
  3.   End
  4. End If
  5.  
  6. Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  7.         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  8.         False, SearchFormat:=False).Activate
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!
Jun 12 '07 #1
Share this Question
Share on Google+
11 Replies


P: 8
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:


Expand|Select|Wrap|Line Numbers
  1. LookFor = InputBox("what is the client name?", "Search")
  2. If LookFor = "" Then
  3.   End
  4. End If
  5.  
  6. Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  7.         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  8.         False, SearchFormat:=False).Activate
  9.  
  10. y = ActiveCell.Row
  11.  
  12.  
  13. 'bunch of code to take values from spreadsheet and put them into a form...
  14.  
  15.  
  16. Do Until z = vbYes
  17.   z = MsgBox("Is this the correct client?", vbYesNoCancel)
  18.   If z = vbNo Then
  19.     Cells.FindNext(After:=ActiveCell).Activate
  20.     y = ActiveCell.Row
  21.     'bunch of code to take values from spreadsheet and put them into a form...
  22.   End If
  23.  
  24.   If z = vbCancel Then
  25.     frmClient.txtRows.Text = fnRow
  26.     fnScrew
  27.   End If
  28.  
  29.   If z = vbYes Then
  30.     frmClient.txtName.SetFocus
  31.   End If
  32. Loop
Jun 13 '07 #2

kadghar
Expert 100+
P: 1,295
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
Jun 13 '07 #3

P: 8
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.
Jun 14 '07 #4

kadghar
Expert 100+
P: 1,295
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:

Expand|Select|Wrap|Line Numbers
  1. lookfor = InputBox("what is the client name?", "Search")
  2. If lookfor = "" Then
  3.   End
  4. End If
  5.  
  6. Flag1:
  7. If Not Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  8.     :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  9.     False, SearchFormat:=False) Is Nothing Then
  10.  
  11.   Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  12.       :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  13.       False, SearchFormat:=False).Activate
  14.  
  15.   If MsgBox("Is this the correct client?", vbYesNoCancel) <> vbYes Then
  16.     GoTo Flag1
  17.   End If
  18. Else
  19.   MsgBox ("The client is not in the list")
  20. End If
Jun 14 '07 #5

kadghar
Expert 100+
P: 1,295
oh, and you can replace all that macro stuf with something like:
Expand|Select|Wrap|Line Numbers
  1. if not cells.find(lookfor) is nothing then
  2. cels.find(lookfor).activate
or something like that
Jun 14 '07 #6

P: 8
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!
Jun 14 '07 #7

kadghar
Expert 100+
P: 1,295
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
Jun 14 '07 #8

100+
P: 132
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....
Jul 12 '07 #9

kadghar
Expert 100+
P: 1,295
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.
Jul 12 '07 #10

100+
P: 132
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??
Jul 12 '07 #11

Expert 5K+
P: 8,434
...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.
Jul 13 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.