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: - LookFor = InputBox("what is the client name?", "Search")
-
If LookFor = "" Then
-
End
-
End If
-
-
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
-
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
-
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!
11 10300
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: - LookFor = InputBox("what is the client name?", "Search")
-
If LookFor = "" Then
-
End
-
End If
-
-
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
-
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
-
False, SearchFormat:=False).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:=ActiveCell).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.txtRows.Text = fnRow
-
fnScrew
-
End If
-
-
If z = vbYes Then
-
frmClient.txtName.SetFocus
-
End If
-
Loop
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
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.
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: - lookfor = InputBox("what is the client name?", "Search")
-
If lookfor = "" Then
-
End
-
End If
-
-
Flag1:
-
If Not Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
-
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
-
False, SearchFormat:=False) Is Nothing Then
-
-
Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
-
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
-
False, SearchFormat:=False).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
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
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!
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
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....
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.
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??
...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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tom |
last post by:
Wta is the code structure to put Rst.FindNext in a loop? I'm using what I show
below. It works fine but my instinct says it should be in some standard loop
rather than using the GoTo structure.
...
|
by: Atchoum |
last post by:
I have a DLL that opens an Excel workbook and add-in.
If I close the app that calls the DLL before closing Excel, when I close
Excel everything is fine.
But if I close Excel while the app is still...
|
by: Jono |
last post by:
Hello,
I've been getting this message when closing excel (not necessarily when closing the workbook by itself, but when closing Excel and the workbook at the same time):
...
|
by: Shelley |
last post by:
Compare Current Year Worksheet with Previous Year Worksheet and if SSN exists in Current Year Worksheet & Not in Previous Year - Copy this Row from Current Year Worksheet & Paste into Previous Year...
|
by: Corster |
last post by:
I went through a great deal of hassle to figure this out for myself, but now it is complete, I would like to share it with the world!
I know afew other people have had trouble with FindFirst and...
|
by: atrottier |
last post by:
I am tring to import an Excel file directly to a table in Access 2003. The code runs but it locks up the app and I need to do a ctrl/alt/del to get out. Here is the code:
Private Declare Function...
|
by: grego9 |
last post by:
I am trying to write some code to get Excel 2000 to insert a blank row wherever there is a "NO" in column O (above the NO). The code below nearly does this - but not quite!. The problem I have is...
|
by: savakis |
last post by:
Can onyone please help me please.
I have duplicate items in column A:A which I find via combobox1. These items are placed in listbox1. I then fill in textbox1 and textbox2 for column C and D, and...
|
by: accessvbanewbie |
last post by:
I would like to export a recordset from access to excel but after each record is exported I want to insert a new row.
The first recordset does this ok. However, the second recordset onwards does not...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |