Results 1 to 9 of 9

Thread: Confusion on .Find
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Location
    Heathfield
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Confusion on .Find

    Hi guys

    I am somewhat confused, I seem to be having difficulty passing a variable to .Find.

    this bit of code works as it should

    Code:
    Sub FindDate()
    Dim dDate As Date
    Dim c As Range
    'need to pass the date to sub as a variable.. how what sort?
    
    dDate = Range("d1").Value
    With Worksheets(1).Range("A10:AG10")  'range with dates
       Set c = .Find("20/03/05")  'find this date in row 10
        Range(c.Offset(1, 0), c.Offset(33, 27)).Select  'range just below selected date
          
    End With
    
    End Sub
    however when I try to pass the date as a variable it finds the last entry in the row then sets the range.


    Code:
    Sub FindDate()
    Dim dDate As Date
    Dim c As Range
    'need to pass the date to sub as a variable.. how what sort?
    
    dDate = Range("d1").Value
    With Worksheets(1).Range("A10:AG10")  'range with dates
       Set c = .Find(dDate)  'find this date in row 10 goes to end of row?
        Range(c.Offset(1, 0), c.Offset(33, 27)).Select  'range just below selected date
          
    End With
    
    End Sub
    I do not understand why this is happening any one got any ideas.....

    cheers

    martin

  2. #2
    Board Regular
    Join Date
    Nov 2004
    Location
    NB
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tip:
    When U use Find method, always define all the properties, otherwise excel will use the last ones used

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Martin,

    The Find method can sometimes be a little tricky to use with dates. Try using DateValue with your variable, like this :

    Set c = .Find(DateValue(dDate))

    Does that help?
    Richie

  4. #4
    Board Regular
    Join Date
    Jan 2005
    Location
    Heathfield
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi Richie

    using the datevalue did help to find the date but now it throws up an

    object variable or with block variable not set

    and highlights this row

    Code:
    Range(c.Offset(1, 0), c.Offset(33, 27)).Select  'range just below selected date
    which seems to indicate that the code thinks that c variable isnt set.????

    however it has been defined as a Range and set in this line

    Code:
    Set c = .Find(DateValue(dDate))
    or at least i thought so???

    could there be another reason for this error?

    cheers
    martin

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,152
    Post Thanks / Like
    Mentioned
    61 Post(s)
    Tagged
    6 Thread(s)

    Default

    martin

    The reason for this could be because the date hasn't been found.

    Like Richie said dates can be notoriously difficult when using the .Find method.

    Another problem with the .Find method is if you don't supply all the arguments then it will default to those used previously.
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Jan 2005
    Location
    Heathfield
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Norrie

    OK so if its not finding the date even though it is there, is there another way of finding a date in a range? all I need is the cell reference so that i can offset the range i wish to copy and paste

    otherwise

    is there some place i can get all the arguments neccessay for the .find method....

    cheers

    martin

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,152
    Post Thanks / Like
    Mentioned
    61 Post(s)
    Tagged
    6 Thread(s)

    Default

    martin

    1 You could loop through the range, but obviously that may be time consuming.

    2 Best way is to turn on the macro recorder and do it manually. that should generate code that you can use for your find.

    To actually just get the list of arguments, select Find in the VBA editor and hit F1. That should open up the relevant page in help.
    If posting code please use code tags.

  8. #8
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Find looks for the value of a cell, not how it may be formatted. When you use .Find("20/03/05") and it works, suggests to me that the values saved in the range you are looking at are not dates at all, but saved as text, while D1 is an actual date.

    If the range was saved as actual dates then this would fail because a dates value is actually a number, so the numerical value of 20/3/05 is 38431, and thats what find is looking for when you used the ddate variable.

    If this is the case then change Dim dDate as Date to Dim dDate as string and amend dDate = Range("d1").Value to dDate = Range("d1").Text.

  9. #9
    Board Regular
    Join Date
    Jan 2005
    Location
    Heathfield
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    got it sussed guys

    should have been using

    Code:
    Set c = .Find _
       (what:=DateValue(dDate), LookIn:=xlFormulas)
    not .....

    Code:
    Set c = .Find _
       (what:=DateValue(dDate), LookIn:=xlValues)
    this works okay, only problem is most of my dates are via formulas...
    eg

    A1....................B1
    20/03/2005 =A1+1

    as it is looking in formulas it couldnt find the date.....:0

    just got to change all my dates to true dates,


    cheers

    martin

Some videos you may like

User Tag List

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
  •