Inputbox search on specify sheets

[ddh]

I have a workbook with sheets (A thru Z). I am wanting to only search in sheets (A thru C).I cannot figure out what I am doing wrong.Thank you for your help.

Sub LineSearchTESTA1()
Dim MyValue, MyFindNext
Dim sht As Worksheet
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
If MyValue = "" Then
[C3].Select
Exit Sub
End If
On Error GoTo err_Trap
Columns(3).Find(What:=MyValue).Activate
For Each sht In Worksheetssht.
Worksheets("SheetA", "SheetB", "SheetC") = Activate
MyFindNext = vbYes
Do Until MyFindNext <> vbYes
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then
Exit Sub
End If
Columns(3).FindNext(After:=ActiveCell).Activate
Loop
On Error GoTo 0
Exit Sub
err_Trap:
If Err.Number = 91 ThenAns = MsgBox("Search could not find '" & _
MyValue & "'." & vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbNo Then
Exit Sub
Call LineSearchTESTB2Else
MsgBox Err.Number & ": " & Err.Description
End If
Next sht
End Sub

[Jacob Hilderbrand]

Columns(3).Find

Do you just want to search in Column 3 on the sheets?
Try something like this to get you started.

Option Explicit

Sub Search()
Dim ws              As Worksheet
Dim Cel             As Range
Dim Results         As String
Dim SearchString    As String
Dim FirstAddress    As String
SearchString = "a"
    For Each ws In Sheets(Array("A", "B", "C"))
        With ws.Cells
            Set Cel = .Find(what:=SearchString, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=True)
            If Not Cel Is Nothing Then
                FirstAddress = Cel.Address
                Do
                    Results = Results & ws.Name & " - " & _
                    Cel.Address(False, False) & vbNewLine
                    Set Cel = .FindNext(Cel)
                Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
            End If
        End With
    Next
MsgBox Results
End Sub

[ddh]

Thank you very much for your for your help.

I am sorry that I did not make myself clearer.
This is what the code is doing.
In the Inputbox I type in a name and it will GOTO that cell in Column 3 and then it will ask if I want to search again and if I do then I hit enter in it will take me to the next cell.
I just would like to know what and where in the code that I have
to make it search on just certain pages, I know how to make it search ALL pages, but not search certain pages.
"The line of code"

For Each sht In Worksheets
        sht.Worksheets("SheetA", "SheetB", "SheetC") = Activate

"And"

Next sht

I cannot get it to work and I don't know if the code is wrong in the wrong place.

Sub LineSearchTESTA1()
Dim MyValue, MyFindNext
Dim sht As Worksheet
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
 If MyValue = "" Then
[C3].Select
Exit Sub
End If
On Error GoTo err_Trap
Columns(3).Find(What:=MyValue).Activate
 
For Each sht In Worksheets
        sht.Worksheets("SheetA", "SheetB", "SheetC") = Activate
MyFindNext = vbYes
Do Until MyFindNext <> vbYes
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then
Exit Sub
End If
Columns(3).FindNext(After:=ActiveCell).Activate
Loop
On Error GoTo 0
Exit Sub
err_Trap:
If Err.Number = 91 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & vbNewLine & _
" " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbNo Then Exit Sub
Call LineSearchTESTB2
Else
MsgBox Err.Number & ": " & Err.Description
End If
Next sht
End Sub

[Jacob Hilderbrand]

Try the line I used to loop through the sheets.

For Each ws In Sheets(Array("A", "B", "C"))

[ddh]

Thank you for your help.

I entered your line of code and changed the "Next sht" to "Next ws" and I must not have it in the correct place, I thought it should be after the "End If". But this did not work? What am I doing wrong.
Thank you for your help.

Sub LineSearchTESTA1()
Dim MyValue, MyFindNext
Dim sht As Worksheet
Dim ws As Worksheet
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
 If MyValue = "" Then
[C3].Select
Exit Sub
End If
For Each ws In Sheets(Array("A", "B", "C"))
On Error GoTo err_Trap
Columns(3).Find(What:=MyValue).Activate
MyFindNext = vbYes
Do Until MyFindNext <> vbYes
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then
Exit Sub
End If
Columns(3).FindNext(After:=ActiveCell).Activate
Loop
On Error GoTo 0
Exit Sub
err_Trap:
If Err.Number = 91 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & vbNewLine & _
" " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbNo Then Exit Sub
Call LineSearchTESTB2
Else
MsgBox Err.Number & ": " & Err.Description
End If
Next ws
End Sub

[Jacob Hilderbrand]

You also need to specify the ws you are working on since it will not be active.

ws.Columns(3)...

Or use

With ws

then just use this.

.Columns(3)...

[ddh]

Thank you for your help. I entered the "ws." and it does not jump over to the next page, Could you enter the code on a sheet and try it and see what I am doing wrong. I have several books on VBA and I am trying to learn and also learn from examples that I see.

Sub LineSearchTESTA1()
Dim MyValue, MyFindNext
Dim sht As Worksheet
Dim ws As Worksheet
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
 If MyValue = "" Then
 [C3].Select
Exit Sub
End If
On Error GoTo err_Trap
ws.Columns(3).Find(What:=MyValue).Activate
For Each ws In Sheets(Array("A", "B", "C"))
MyFindNext = vbYes
Do Until MyFindNext <> vbYes
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then
Exit Sub
End If
Columns(3).FindNext(After:=ActiveCell).Activate
Loop
On Error GoTo 0
Exit Sub
err_Trap:
If Err.Number = 91 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & vbNewLine & _
" " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbNo Then Exit Sub
Call LineSearchTESTB2
Else
MsgBox Err.Number & ": " & Err.Description
End If
Next ws
End Sub

[Jacob Hilderbrand]

You need to put your first Find line inside the loop. I you are still having problems, can you zip your workbook and attach it?

[ddh]

Jake - Thank you for the help.

You need to put your first Find line inside the loop. I you are still having problems, can you zip your workbook and attach it?

I finally figured out how to make a "zip' file and have attached it.
Thank you

[Jacob Hilderbrand]

Try this macro.

Option Explicit
 
Sub LineSearchTESTA1()
Dim MyValue         As String
Dim MyFindNext      As Long
Dim sht             As Worksheet
Dim Ans             As Long
Dim FirstAddress    As String
Dim Cel             As Range
Dim Counter         As Long
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
    If MyValue = "" Then
        [C3].Select
        Exit Sub
    End If
    For Each sht In Sheets(Array("A", "B", "C"))
        With sht
            sht.Activate
            Set Cel = .Columns(3).Find(What:=MyValue)
            MyFindNext = vbYes
            If Not Cel Is Nothing Then
                FirstAddress = Cel.Address
                Counter = Counter + 1
                Cel.Activate
                Do
                    MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
                    Set Cel = .Columns(3).FindNext(Cel)
                Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
            End If
        End With
    Next
If Counter = 0 Then
        Ans = MsgBox("Search could not find '" & MyValue & "'." & _
        vbNewLine & " " & vbNewLine & _
        "Try another search?", 4, MyValue & " not found")
        If Ans = vbYes Then
            Call LineSearchTESTA1
        End If
    End If
End Sub

[ddh]

Jake - Thank you very much for all of your help and the new code.I have attached a file (I had never converted anything to a zip file before, it took me some time to figure out how to do it).

What is a Zip file? And why not a Excel file?

I have some questions about the code.Start on Page A, C1 and click on the Search button, type in Dog. It will take you to Page A, C3 (as it should) (It should then go to Page A C10 next).

If you click find next it goes to Page B, C13(it should then go to Page B, C20 next).If you click find next it goes to Page C, C28(it should then go to Page C, C32 next).

If you click find next it quits (it should then go to Page A, C10 next).If you type in a name that is not on the sheet it takes you Page C which is fine but if you then type in Dog, it stays on Page C, it should start back at Page A

If you tell it No on any sheet it just keeps searching and does not quit until Page C I tried to compare your code to the one I was using to find the differences to make it search all of column 3 on each sheet and keep looping, but I could not figure out what to.

Thank you for your help.

[Jacob Hilderbrand]

Zip is just a compressed file (it just reduces the file size). This saves space for the site and also makes it faster for people to download.

Give this macro a try.

Option Explicit

Sub LineSearchTESTA1(Optional SearchVal As String)
Dim MyValue         As String
    Dim MyFindNext      As Long
    Dim sht             As Worksheet
    Dim Ans             As Long
    Dim FirstAddress    As String
    Dim Cel             As Range
    Dim Counter         As Long
If SearchVal = "" Then
        MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
    Else
        MyValue = SearchVal
    End If
    If MyValue = "" Then
        [C3].Select
        Exit Sub
    End If
    For Each sht In Sheets(Array("A", "B", "C"))
        With sht
            sht.Activate
            Set Cel = .Columns(3).Find(What:=MyValue)
            MyFindNext = vbYes
            If Not Cel Is Nothing Then
                FirstAddress = Cel.Address
                Do
                    Counter = Counter + 1
                    Cel.Activate
                    MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
                    Set Cel = .Columns(3).FindNext(Cel)
                Loop While Not Cel Is Nothing And _
                Cel.Address <> FirstAddress And _
                MyFindNext = vbYes
            End If
        End With
        If MyFindNext = vbNo Then
            Exit Sub
        End If
    Next
If Counter = 0 Then
        Ans = MsgBox("Search could not find '" & MyValue & "'." & _
        vbNewLine & " " & vbNewLine & _
        "Try another search?", 4, MyValue & " not found")
        If Ans = vbYes Then
            Call LineSearchTESTA1
        End If
    End If
    If MyFindNext = vbYes Then
        Call LineSearchTESTA1(MyValue)
    End If
End Sub

[ddh]

Jake - Thank you very much for all of your help.

I copy and pasted your code below the old code I was using and changed the name to

Sub LineSearchTESTAA1(Optional SearchVal As String)

and when I went in and tried to assign it to the button, it did not show up.
I changed the two other places that had the "TestA1".
The only thing different is the (Optional SearchVal As String) and on the old one
it has (). I inserted a new Module and pasted it in there and it still did not show up. This has never happened before.
Would you be interested in see how I am using this code?
Thank you.

Jake - I figured it out.
I put (MyValue) after the "

Call LineSearchTESTA1(MyValue)

"

Well it did work, I put a name in the search box that was not on the sheet.
It would not let me search again or get out, I had to crash out of the sheet.
Then I pasted the code in again and I have pasted the same code in and now it does not show up for me to assign the code to.
Than you for all of your help.

If Ans = vbYes Then 
     Call LineSearchTESTA1 
End If 
End If 
If MyFindNext = vbYes Then 
     Call LineSearchTESTA1(MyValue)

[Jacob Hilderbrand]

Ok, this macro should work fine for you.

Option Explicit
 
Sub LineSearchTESTA1(Optional SearchVal As String)
Dim MyValue         As String
    Dim MyFindNext      As Long
    Dim sht             As Worksheet
    Dim Ans             As Long
    Dim FirstAddress    As String
    Dim Cel             As Range
    Dim Counter         As Long
If SearchVal = "" Then
        MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
    Else
        MyValue = SearchVal
    End If
    If MyValue = "" Then
        [C3].Select
        Exit Sub
    End If
    For Each sht In Sheets(Array("A", "B", "C"))
        With sht
            sht.Activate
            Set Cel = .Columns(3).Find(What:=MyValue)
            MyFindNext = vbYes
            If Not Cel Is Nothing Then
                FirstAddress = Cel.Address
                Do
                    Counter = Counter + 1
                    Cel.Activate
                    MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
                    Set Cel = .Columns(3).FindNext(Cel)
                Loop While Not Cel Is Nothing And _
                Cel.Address <> FirstAddress And _
                MyFindNext = vbYes
            End If
        End With
        If MyFindNext = vbNo Then
            Exit Sub
        End If
    Next
If Counter = 0 Then
        Ans = MsgBox("Search could not find '" & MyValue & "'." & _
        vbNewLine & " " & vbNewLine & _
        "Try another search?", 4, MyValue & " not found")
        If Ans = vbYes Then
            Call LineSearchTESTA1
        Else
            Exit Sub
        End If
    End If
    If MyFindNext = vbYes Then
        Call LineSearchTESTA1(MyValue)
    End If
End Sub

Since there is an Argument in the macro it will not appear in the macro box when you try to add it to the button on the worksheet. You can type the name in manually and attach it to the button that way.

Or you can remove the optional argument, then attach the macro to the button, then put the optional argument back in.

[ddh]

Jake - Thank you very much for all of your help.

It works just like the old code that was set up to do one page. I never knew that all you had to do was type in the name. I have learned so much from the code that you used.

If I keep trying to read and learn then in maybe ten years I will know 10% of what you know. I am trying to find a school class here in Indiana that will teach VBA so I can learn. I would like to be able to do the things that you do. I can understand the code after it is writen but I have a hard time writing it from scratch. Thank you very much for all of your help. If you are interested I can show you how the code that you wrote will be used.

[Jacob Hilderbrand]

You're Welcome

I would like to see the final product if you want to post it for us.

[ddh]

Jake - Below is a breakdown of what I having been doing.
I started this out some time ago and I never thought I would not have a job by now (the economy in Wayne County, Indiana is very bad). I never thought I would have
20,000 lines much less 37,148. If I keep going I will exceed the 65,000 sheet
lines. So instead of wait until I reach that limit I thought I would do it now.
The list has been on one page so I am in the process of setting up each letter
of the Alphabet and having the being able to last forever, because I won't ever get
65,000 of one letter. When I am ready to enter a new line I do a search to take me to
that line, your code will allow me to do the same thing just over 26 sheets.
I set up a lot of my own code but I am limited on my knowledge, that is why I ask for help. The database has MANY things on it the is not listed in the overview.
The VBA training that this site would provide I think would be just like the SIX books I have on VBA, I still get very confussed and need some one on one help when I get stuck or don't understand (which is a lot). I need someone to explain to me one on one, I have done the Cobalt 84 and some Fortran programming a long time ago.
Thank you very much for your help.






I am a 62 year old accountant and not many businesses are willing to hire someone the same age as their
employee that is getting ready to or has retired. I have a lot of difficultly presenting my resume to businesses for a potential interview and employment.I started developing a fax and e-mail list so I would have a way for my resume to be viewed. I still don?t have a job,so it has not worked as I expected. Now the list is something to do that helps keep my mind and days active.This Fax/E-mail database is on an Excel spreadsheet and added to daily. The database contains six different sections and currently contains 37,148 Lines with a file size of 46.40 MBFor a business to qualify for entry to the database it must have data in one of the four categories. (1) POB (2) Fax Number (3) Web Site (4) E-Mail AddressCategory count as of 03/06/059,578 19,51322,414 21,311 Area boundry -( IN ) - Indianapolis, Kokomo, Brookville, ( OH ) - Greenville, Springfield, Cincinnati INDIANA Area covered - 19 counties, 324 towns, 6,647 Square Miles, 1,969,938 Population. OHIO Area covered - 9 counties, 255 towns, 4,011 Square Miles, 2,382,697 Population.A. MAIN DATABASE: Data is entered daily to the 13 data category columns. (1) Company Name(2) Number of Employees (3) Address (4) POB (5) Town (6) State (7) ZIP Code (8) Contact Person (9) Telephone Number (10) Fax Number (11) Web Site (Hyperlink) (12) Business Type (13) E-Mail Address (Hyperlink) In the main database along with businesses are Schools, Realtors, Churches, Associations and Societies with each persons or department e-mail address and phone number extension if available and other miscellaneous entries and business chain store numbers and many other forms of information.B. ALPHABET SUMMARY: This Alphabet list contains individual Letter Totals from the Company Name (1) Columnof the Main database ( A ) A content percent and rank column and a bar graph are included. There are 3 sorting options available. (1) Ascending (2) Descending (3) AlphaAlphabet rank as of 03/06/05Letter - " C "ranks first with3,423 LinesC. STATE and TOWN SUMMARY: This State Section contains individual State Totals from the State (6) Column of the Main database ( A ). Town Section contains Town Totals from the Town (5) Column of the Main database(A) A content percent and rank column and a bar graph are included." Indiana " ranks first with " Indianapolis "ranks first withState/Town rank as of 03/06/0527,025 Lines10,108 LinesD. AREA CODE SUMMARY: This Area Code list contains individual Area Code Totals from the Area Code (11) column of the Town database ( D ) A content percent and rank column and a bar graph are included. There are 2 sorting options available - (1) Area Code (2) TotalIndiana A/Code " 812 " ranks first withOhio A/Code " 740 " ranks first withArea Code rank as of 03/06/05 756 Town Lines 439 Town LinesE. TOWN DATABASE: This Town Database contains the following data columns with data from the 2002 Census. (1) Town Name (2) Town Population (3) % Town Population of County Population (4) Town Square Miles (5) County Name (6) Map Location (7) County Square Miles (8) County Population (9) % County Population of State Population (10) Zip code (11) Area Code (12) County Code (13) Number of Cities in the County. This section is designed to allow typing a town's name in the top left cell and all 13 data categories about the town will be displayed from the database in the top line or you can scroll to the town in the list. The database can be sorted by each of the above categories except (3), (6), (9), (13). This section includes a state map allowing you to locate the county on the map where the town is located using the alpha numerical code in the Location column (6) of the database.F. COUNTY DATABASE: This County List contains the following data columns with data from the 2002 Census (1) County Name (2) County Population (3) % County Population of State Population (4) County Land Square Miles (5) County Water Square Miles (6) County Total Square Miles (7) Number of Towns in County (8) % County Towns of Total Counties (9) Average Town Population (10) Average Population per Square Mile (11) Average Square Miles per Average Town. The database can be sorted by each of the above categories except (3) and (8).Each section also contains MANY other special features not listed above.This list is just a hobby, like a person that collects a ball of string. I am not selling this list, I am not a business.I am just trying find a job. It is on a CD-RW.

[ddh]

Jake - Thank you for your help.
When I enter a Name that is not in the database and it asks for another search and I type in a good name and it will find it then it will not stop.

I have been unable to insert the

If MyFindNext = vbNo Then
Exit Sub

in the correct place.
If I enter a name that is not in the database several times and then try to quit, it will not quit on the first "No".
Other then that the code works perfect.
Thank you very much.

Option Explicit
 
Sub LineSearchTEST01(Optional SearchVal As String)
Dim MyValue As String
Dim MyFindNext As Long
Dim sht As Worksheet
Dim Ans As Long
Dim FirstAddress As String
Dim Cel As Range
Dim Counter As Long
 
If SearchVal = "" Then
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
Else
MyValue = SearchVal
End If
If MyValue = "" Then
[C3].Select
Exit Sub
End If
For Each sht In Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
"W", "X", "Y", "Z"))
With sht
sht.Activate
Set Cel = .Columns(3).Find(What:=MyValue)
MyFindNext = vbYes
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
Set Cel = .Columns(3).FindNext(Cel)
Loop While Not Cel Is Nothing And _
Cel.Address <> FirstAddress And _
MyFindNext = vbYes
End If
End With
If MyFindNext = vbNo Then
Exit Sub
End If
Next
If Counter = 0 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbYes Then
Call LineSearchTEST01
Else
Sheets("A").Select
[C3].Select
Exit Sub
End If
End If
If MyFindNext = vbYes Then
Call LineSearchTEST01(MyValue)
End If
If MyFindNext = vbNo Then
Exit Sub
End If
End Sub

[ddh]

It is possible to correct the problem with the code?
Thank you.

[Anne Troy]

ddh: It's tax time, right? Go sign up at www.ingenio.com. It costs you nothing to sign up. If you are asked for a referral, enter:

-Dreamboat

and I'll send you the referral fee I earn.

And...I mean that you should become a "provider" there.