Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By mdocton

VBA Find Method

This is a discussion on VBA Find Method within the Excel Questions forums, part of the Question Forums category; Hello, I'm currenlty having problems getting the VBA Range .Find method. What I want in to find a number in ...

  1. #1
    New Member
    Join Date
    Nov 2003
    Posts
    4

    Default VBA Find Method

    Hello,


    I'm currenlty having problems getting the VBA Range.Find method. What I want in to find a number in the given range...however the values in the range are all being determined by formulas (don't know if that is causing the problem)...

    Anyway, the current statement I'm using is...
    Set foundCell = FuncAreaRange.Find(dblMax,Lookin:=xlValues)

    where foundCell and FuncAreaRange are Range Variables and dblMax is a double variable...

    Any ideas what I could be doing wrong? The statement currently returns Nothing..

    Thanks in advance for any help!

    ph

  2. #2
    Mat
    Mat is offline
    Board Regular Mat's Avatar
    Join Date
    Sep 2003
    Location
    Montréal, Québec
    Posts
    506

    Default Re: VBA Find Method

    If you don't mind changing the selected cell:

    FuncAreaRange.Select
    Selection.Find(What:=dblMax, LookIn:=xlValues).Select
    Set Foundcell = Selection

    For a Reason I don't know,

    This works:
    Range("A1:A10").Select
    Selection.Find(What:=dblMax, LookIn:=xlValues).Select

    This gives an error:
    Range("A1:A10").Find(What:=dblMax, LookIn:=xlValues).Select
    Mat
    "I will not compromise my ideals, music or nap schedule" - Trent Lane

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677

    Default Re: VBA Find Method

    I duplicated your code with no problems. Some things to check:

    1. Is your range referring to the correct sheet?
    2. Are you sure the value you are searching for is exactly the same?

    Both will cause a return of Nothing.

    K

  4. #4
    Mat
    Mat is offline
    Board Regular Mat's Avatar
    Join Date
    Sep 2003
    Location
    Montréal, Québec
    Posts
    506

    Default Re: VBA Find Method

    Quote Originally Posted by kkknie
    I duplicated your code with no problems. Some things to check:

    1. Is your range referring to the correct sheet?
    2. Are you sure the value you are searching for is exactly the same?

    Both will cause a return of Nothing.

    K
    Yes, didn't tried with it exactly before, but it does work.

    Thought it was because of the .Find cannot be used with a variable Range, only Selection (as the example I posted before, with Range("A1:A10")) But think I've done something wrong the first time since it seems to work now...

    EDIT: Tried AGain, it wasn't working for real because the first time I had the "After:=ActiveCell," part in the .Find, so that is why I got an error....
    Mat
    "I will not compromise my ideals, music or nap schedule" - Trent Lane

  5. #5
    Board Regular
    Join Date
    Aug 2010
    Posts
    70

    Default Re: VBA Find Method

    I'm really new to this whole VBA sub/function/range etc and am trying to use the Find function.

    I'm looking for the EXACT contents for a cells which I know exists.

    I'm not sure if a function must have an input or if I should use a sub and I do not understand how the Find Method really does work. I've tried finding examples and explanations, but still do not understand. Code is below. Please help.


    Public Function findCell() As Range
    Dim SearchRange As Range
    Dim FoundCell As Range
    Dim FindWhat As Variant
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LooAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    Set SearchRange = ThisWorkbook.Sheets(1).Range("A5:IV65536")
    Set FindWhat = "Setting temp."
    Set LookIn = xlValues
    Set LookAt = xlPart
    Set SearchOrder = xlByRows
    Set MatchCase = False
    Debug.Print FindWhat.Text
    Set FoundCell = SearchRange.Find(FindWhat:=FindWhat, _
    LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    MsgBox FoundCell.Text

    End Function

  6. #6
    Board Regular
    Join Date
    Aug 2010
    Posts
    70

    Default Re: VBA Find Method

    I've update the code on this a bit and I want to return a specific cell. However, the error message I recieve is 'Run Time error 91' Object variable or with block Variable not set

    The Debug highlights the line starting with "Set FoundCell...."

    Public Function findCell() As Range
    Dim SearchRange As Range
    Dim FoundCell As Range
    Dim FindWhat As Variant
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LooAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    Set SearchRange = ThisWorkbook.Sheets(1).Range("A5:IV65536")
    FindWhat = "S"
    LookIn = xlValues
    LookAt = xlPart
    SearchOrder = xlByColumns
    MatchCase = False
    Set FoundCell = SearchRange.Find(FindWhat, , LookIn, LookAt, SearchOrder, xlNext, MatchCase, , SearchFormat:=False)

    End Function

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    45

    Default Re: VBA Find Method

    I know this is an old post but, just in case someone else has a similar query, could the problem be misspelling in;
    Dim LooAt As XlLookAt
    James Snyder likes this.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com