Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    LookIn:=xlValues not working

    Hi,

    This codes are working for numerical data but not for alpha numeric.
    for example. 100000311 then it gets the value but if WLCOMBOCC40 then it does not get the value from sheet patch.

    Code:
    Sub Stats()
    Dim I As Long
    Dim mf As Range
    For I = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Set mf = Sheets("patch").Columns(4).Find(What:=Cells(I, 5), LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not mf Is Nothing Then Cells(I, 5).Value = mf.Offset(, 1)
    Next I
    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    xlWhole = looks at the entire/exact entry in the cell to see if it matches.

    Have you checked whether WLCOMBOCC40 has any trailing spaces etc etc??

    ..or try xlPart = looks at the text within the cell for any match.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    foncesa (2016-05-20)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesa,

    100000311 is a value, i.e. Number

    but WLCOMBOCC40 is NOT a value but a String or Text.

    If I read what you are trying to do correctly I don't think you want a Find operation anyway.

    You're either trying to:
    1. Looping trying to find if the value in Col E is present in Col D of the same row?

    -OR-

    2. Trying to find if the values in Col E show up any where in Col D?

    This is where some test data and expected results would come in real handy to see what you are after.

    In the first case above you could use code similar to this (note I'm searching Col A for the values in Col E and writing the results to Col B).

    foncesa.PNG

    Using:
    Code:
    Option Explicit
    
    Sub Stats()
       
       Dim I  As Long
       Dim mf As Boolean
       
       For I = 2 To Cells(Rows.Count, 1).End(xlUp).Row
       
          mf = IIf(InStr(CStr(Cells(I, 1)), Cells(I, 5)) > 0, True, False)
                                              
          If mf Then
            Cells(I, 2) = "Found"
          Else
            Cells(I, 2) = "NOT Found"
          End If
          
       Next I
       
    End Sub 'Stats
    Of course, if the 2nd scenario is the correct one post back with the sample data and we'll work on it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    foncesa (2016-05-20)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Lets clear up a few things.

    1. The .Find method ignores all hidden and filtered cells i.e. applies to visible cells only, so if what you are looking for is in a 'hidden range', it won't find it, no matter what you use.

    2. The find method is looking at what is in the cells. The cells actual 'content', and what is actually 'displayed' may be different. This is why we have LookIn:=xlValues and LookIn:=xlFormulas

    Lets clear up the difference:
    LookIn:=xlValues means look at what is displayed in the cell (so this means displayed numeric values, displayed text strings, displayed formatted dates etc etc.).

    LookIn:=xlFormulas means look at what is seen in the formula bar for the cells.

    So the .Find method is always a text comparison, regardless of whether you are searching for 'text' or 'numbers'. It's only looking for text-string matches. If you are looking for numbers, these are just treated as any other 'characters'.

    So if you use say, a date, as your search value e.g. Jan 1, 2016, this does not match 1/1/2016.

    LookIn:=xlFormulas can look at both cell-formula-content and cell-contents (i.e. what is 'shown' in the formula bar)

    You must also choose whether you want to LookAt:=xlPart or LookAt:=xlWhole.
    If you choose xlPart then looking for 'fred' will also find it in 'winifred', 'freddy smith', 'Sir Frederick Jones'.
    If you specify MatchCase:=True then 'Sir Frederick Jones' won't count.

    So if you where searching for 'fred', a cell which had a formula that included those 'characters' e.g.
    =IF(D4="freddy","mercury","") will only be counted if you use LookIn:=xlFormulas and LookAt:=xlPart

    Hope that makes sense!

    zeddy
    Last edited by zeddy; 2016-05-21 at 11:51.

  7. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2016-05-21)

Posting Permissions

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