Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FIND DUPLICATE (2000 sr 1)

    which it is the best solution:
    NOTE: column S is hided

    1)
    ID = var_CAUS & "-" & var_ABI & "-" & var_CAB & "-" & var_NRASS
    Set Found_ID = Sheets("L0785_TOTALE").Columns("S:S").Find(ID, lookat:=xlWhole )
    If Found_ID Is Nothing Then
    or
    2)
    ID = var_CAUS & "-" & var_ABI & "-" & var_CAB & "-" & var_NRASS
    Set Found_ID = Sheets("L0785_TOTALE").Columns("S:S").Find(ID, lookin:=xlFormulas )
    If Found_ID Is Nothing Then

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FIND DUPLICATE (2000 sr 1)

    It will depend on what the search is on, ie...Text, Dates or Formulsa!

    If you are using xlFormulas, the search feature looks at the TRUE content of the cell. So if excel is searching for a date such as 1/11/04 (m/d/yy) it will search based on the serial number and not on the date value in the cell. In this example it will therefore not find 11/11/04, as the serial number is different.

    Using xlWhole is only going to work well if the field is text.
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FIND DUPLICATE (2000 sr 1)

    ... In my case is a strng in txt format

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FIND DUPLICATE (2000 sr 1)

    If it is text, there will be no difference in using xlWhole or xlFormula.
    To get the best result for text: change Lookat:=xlWhole and Lookin:=xlValue
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FIND DUPLICATE (2000 sr 1)

    TKS, for the exaplain...

    but about your experince wich is the best:

    Lookat:=xlWhole
    or
    Lookin:=xlValue

    or one of this give the same best result?

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FIND DUPLICATE (2000 sr 1)

    xlWhole and xlValues/xlFormulas are 2 different argument values of the Find method. They do different jobs within the Find command.
    xlValues/xlFormulas tells the Find command to what to look in. It specifies if the search must look in the value of the cell or look in the formula. For example if a cells content is "11", the VALUE is always 11 if it is 11 or ="1"&"1", but xlFormulas will only find the formula ="1"&"1", and never find "11".
    xlWhole is a different argument value, hense the argument name is Look at! Using this will instruct the search feature to search for an exact match and complete match of the cell value irrespective of if you use xlValues or xlFormulas.

    To sum up:
    If you use Look at:=xlWhole, it searches for an exact and complete find in a value or formula, based on what you specify in Look in!
    If you only use Look in, it will search in a formula or in a value, but not apply the match whole value!

    For your case, you must use both to get the most accurate result...Look at:=xlWhole, Look in:=xlValues

    Hope this is clearer!
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FIND DUPLICATE (2000 sr 1)

    ... big explain!
    I take the variables wit this instruction from a txt file:

    var_CAUS = Mid(riga, 50, 2)
    var_ABI = Format(Mid(riga, 17, 5), "#00000")
    var_CAB = Format(Mid(riga, 23, 5), "#00000")
    var_NRASS = Val(Trim(Mid(riga, 45, 10)))
    ...

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FIND DUPLICATE (2000 sr 1)

    Have you declared the ID variable as string : Dim ID as String

    If you have the declaration, then ID will be seen as s string value, and the Find will work effectively!
    Regards,
    Rudi

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FIND DUPLICATE (2000 sr 1)

    oh!oh! ...

    But to the default if one variable is not declared to the default is a String, or not?

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FIND DUPLICATE (2000 sr 1)

    You are half correct....yes

    If you do not declare a variable, the variable becomes VARIANT...meaning it can store any value....date, text, number etc.
    It is best to declare it though. Say you store a value like 123. If you do not declare this explicitly as STRING, the value will be stored as a INTEGER or LONG INTEGER.
    If you have any symbol or Alphabetical character in it like A123, then it is stored as STRING. (as you mentioned)

    Best to declare your variable though. Use the Statement : Option Explicit at the top of your modules to enforce this!
    Regards,
    Rudi

Posting Permissions

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