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 10301
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: 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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |