Results 1 to 9 of 9
  1. #1
    tdhw
    Guest

    Find method? (97)

    I tried to use Find method to search a string (contract No.) from an Excel file in another drive in a local network. The searched string can be at any sheet of total 6 to 8 sheets of the file but always in Column B. Basically I wrote my code same as what the Help file example for Find method except diff variable names and I use intCount index instead of a number:

    set c = Worksheets(intCount).Range("B1:B50").Find(Contract No, , , , xlByRows )

    but it failed to work. Even the letter "f" in "find" can not be changed to "F" automatically after typing the code. The file can be opened and total number of sheets is correctly counted by my code before the finding code. I have no idea why "find" didnt work, even I put exact sheet Number where the Contract Number exists, c still returns "Nothing".

    Another question, if I can make "Find" work, then by what code can I get the row number of the found cell which contains the searched Contract Number?

    TIA

    David

  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: Find method? (97)

    I'm a bit unclear of what exactly your Find is looking for, but unless ContractNo is a string Variable containing "ContractNo", the string to be found should be in quotes. In a quick and dirty test, this worked for me:

    Sub FindIt()
    Dim rngFound As Range
    Dim intCounter As Integer
    For intCounter = 1 To Sheets.Count
    Set rngFound = Sheets(intCounter).Range("B1:B50").Find("ContractN o")
    If Not rngFound Is Nothing Then
    MsgBox rngFound
    MsgBox rngFound.Address
    MsgBox rngFound.Row
    Else
    MsgBox "not on this sheet"
    End If
    Next intCounter
    End Sub

    The third MSgBox in the code provides the Row number. The code needs to be improved for multiple occurences of "ContractNo" on the same sheet; depends on your objective.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find method? (97)

    Well, as usual, John beat me, but I had an excuse: the boss was on the internet computer! Hopefully, John's example fixed you up, but since mine was a little different, I thought that I would post it. Let us know if you need more help, but it wiil be Friday. Tomorrow I'm a <IMG SRC=http://www.mtl.com/sam/images/pilgrim.gif>
    <pre>Option Explicit
    Sub FindContract()
    Dim strWorkbook As String
    Dim thisName As String
    Dim wb As Workbook
    Dim strContract As String
    Dim ws As Worksheet
    Dim c As Range
    thisName = ActiveWorkbook.Name
    strWorkbook = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls", , _
    "Open Contract File")
    Set wb = Workbooks.Open(Filename:=strWorkbook)
    strContract = Application.InputBox("Enter desired contract number", _
    "Find Contract", , , , , , 2)
    For Each ws In wb.Worksheets
    Set c = ws.Columns.Find(what:=strContract, LookIn:=xlValues, _
    Lookat:=xlPart, searchorder:=xlByColumns)
    If Not c Is Nothing Then
    MsgBox "Contract is on WorkSheet " & ws.Name & _
    ", row " & c.Row
    Exit For
    End If
    Next ws
    If c Is Nothing Then MsgBox "Contract not found!"
    Windows(thisName).Activate
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    tdhw
    Guest

    Re: Find method? (97)

    Thank you, both John and Sam,

    Sorry, I didnt say clearly. "Contract No" was a str variable for diff contract numbers. I actually used variable name strSearch.

    Sam, your code is not secondary. After I added "LookIn:=xlValues" in the parameter list, it worked!! I thought it was for numerical value not for string. Anyway, thanks a lot. You both reacted very quick to solve my problem.

    David

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

    Re: Find method? (97)

    Among Sam's many points in the code, I was looking for the string "ContactNo", but if ContractNo is a variable which contains a number, David, you still search for that number as a String.

    OK, Sam, "while you're up" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>: since coding isn't my forte, I decided to figure out how to implement object.FindNext() in the code, using David's original question. How can I streamline the If Not rngFound is Nothing and Do While pieces of this code so that it still finds every instance of strLookFor? I moved the Not rngFound Is Nothing and rngFound.Address = strFirstFound to different places, but I can't get each "found row" exactly right any better way than this.

    Sub FindAll()
    Dim rngLookIn As Range, rngFound As Range
    Dim strFirstFound As String, strLookFor As String
    Dim intCounter As Integer

    strLookFor = InputBox("Find What? ")
    For intCounter = 1 To Sheets.Count
    Set rngLookIn = Sheets(intCounter).Range("B1:B50")
    Set rngFound = rngLookIn.Find(strLookFor)
    If Not rngFound Is Nothing Then
    strFirstFound = rngFound.Address
    MsgBox rngFound.Row
    Do
    Set rngFound = rngLookIn.FindNext(rngFound)
    If rngFound.Address = strFirstFound Then Exit Do
    MsgBox rngFound.Row
    Loop
    Else
    MsgBox "Nothing on " & Sheets(intCounter).Name
    End If
    Next intCounter
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find method? (97)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > How can I streamline
    <img src=/w3timages/blackline.gif width=33% height=2>

    John, this is how I would do it, except I would use a for each loop as in my original code, and I prefer Excel's InputBox method because it forces the user to enter what you want -- number, string, range, etc. Several guidelines that I used in this new code:
    1) You want to use the Worksheets collection instead of Sheets, since the find method does not work on a chart sheet.
    2) I rearranged the code to eliminate NOT's. Not always possible (excuse the pun), but usually makes the logic more readable.
    3) If possible, eliminate duplicate code: in this case the MsgBox.
    4) Avoid exits from the middle of the loop: again making the logic more readable.

    I really lucked out in this example to be able to do all of that. As you can see in my first example, I violated my no-exit-for rule. I guess my priorities are 1 - works, 2 - easy to read, 3 - short, and 4 - simple logic. Does that help? --Sam

    <pre>Sub FindAll()
    Dim rngLookIn As Range, rngFound As Range
    Dim strFirstFound As String, strLookFor As String
    Dim intCounter As Integer

    strLookFor = InputBox("Find What? ")
    For intCounter = 1 To Worksheets.Count
    Set rngLookIn = Worksheets(intCounter).Range("B1:B50")
    Set rngFound = rngLookIn.Find(strLookFor)
    If rngFound Is Nothing Then
    MsgBox "Nothing on " & Worksheets(intCounter).Name
    Else
    strFirstFound = rngFound.Address
    Do
    MsgBox rngFound.Row
    Set rngFound = rngLookIn.FindNext(rngFound)
    Loop Until rngFound.Address = strFirstFound
    End If
    Next intCounter
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  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: Find method? (97)

    Thank you, Sam. Your guidance is always helpful and exactly what I was looking for. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I'm off to learn the InputBox method.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find method? (97)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > learn the InputBox method
    <img src=/w3timages/blackline.gif width=33% height=2>
    Just type Application.InputBox and press F1 to get a good help file. The only tricky part is how to get a range from the user and still trap the cancel button. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Find method? (97)

    Thanks, have already taken all your suggestions into account. As to the Range capture traps, "I'll worry about that later." I had better get over my turkey overeating stupor, away from the keyboard and out of the house for a bit: 5" of wet snow to shovel.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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