Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can the lounge help with this? (eXcel 2000)

    I have a search button which works fine however I want it to have the find next function on it at the moment it just finds the first item then shuts off here is the code for what I have at the moment:

    Sub myfind()
    OrigSheet = ActiveSheet.Name
    'Search all sheets for InputBox string.

    Dim Message, Title, Default, SearchString
    Message = "Please enter " ' Set prompt.
    Title = "Search your surplus stock" ' Set title.
    Default = "" ' Set default.
    ' Display message, title, and default value.
    SearchString = InputBox(Message, Title, Default)
    Set S = Sheets.Application
    For Each S In Application.Sheets
    With S.Range("A1:IV65536")
    Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
    If F Is Nothing Then
    Sheets(OrigSheet).Select
    Else
    Location = F.Address
    S.Select
    Range(Location).Select
    Exit For
    End If
    End With
    Next S
    End Sub

    any suggestions?


    eXistenZ

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    You need to investigate FindNext. You may also want to consider Jan Karel's excellent <!post=FlexFind,133044>FlexFind<!/post> code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    I cannot get the flexfind code to download it says it doesnt exist

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    Hmm, I can't either. I have attached the version I d/l'd at that time, see if you can get it.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    thanx I will look into it although at first glance it looks more involved than what I require but I shall look and see

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    Your code times out after the first occurence of the find situation being true due to the placement of the exit for statement. After it finds the first occurence, it exits the for statement and does not look further. Try this:

    <pre>Private Sub CommandButton1_Click()
    OrigSheet = ActiveSheet.Name
    Dim S As Worksheet
    Dim Message, Title, Default, SearchString
    Message = "Please enter "
    Title = "Search your surplus stock"
    Default = ""
    SearchString = InputBox(Message, Title, Default)
    For Each S In Application.Sheets
    With S.Range("A1:IV65536")
    Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
    If F Is Nothing Then
    Sheets(OrigSheet).Select
    Exit For
    Else
    Range(F.Address).Select
    'Instead of selecting, do something other here
    End If
    End With
    Next S
    End Sub
    </pre>


    I do not believe the results would be quite what you intended however, since the selection takes place more rapidly that you could see what was selected. Why not think about putting the results of the search say either in a different sheet, or set 6 or so rows at the top of the sheet and put the results of the find there?

    Also, if you are looking up specific records, you might need to define the column you are searching in, or the search might return counts when you are looking for an item number and vice-versa.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    I understand that Jan Karel's code may appear to be overkill, but it shows how to approach these kind of issues. It would help if you could tell exactly what the user should do upon finding the value sought, and also if there will be multiple instances of the value, or if you can be certain there'll be only one instance of the value.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the lounge help with this? (eXcel 2000)

    You may also find it at:

    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •