Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    find method (2002 xp)

    Hi all,
    I am confused about the find method, when I am in the active sheet i have no problem finding my value, but when I am trying to set the sheet then find, I run into problems. Do I need to be on the active sheet to use the find method?

    Option Explicit
    Private Sub Lbcrew2_Click()
    Dim oscell As Range
    Dim irow As Integer
    Dim k As String
    k = "Somevalue"
    With Worksheets(2).Range("A1:A500")
    Set oscell = Cells.Find(k, , , , , xlNextTrue)
    With oscell
    irow = .Row
    End With
    End With

    End Sub

  2. #2
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find method (2002 xp)

    The unqualified Cells in the line
    Set oscell = Cells.Find(k, , , , , xlNextTrue)
    references the Active Sheet. Adding a dot
    Set oscell = <font color=red> <big>.</big> </font color=red> Cells.Find(k, , , , , xlNextTrue)

    will cause it to reference the sheet specified in the With statement

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

    Re: find method (2002 xp)

    Make sure you use the other arguments of the find method, especially the start cell. Make sure it starts on the sheet you want it to.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: find method (2002 xp)

    Thank you guys!

Posting Permissions

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