+ Reply to Thread
Results 1 to 13 of 13

.Find Method Returning Incorrect Values For Whole Numbers

  1. #1
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    .Find Method Returning Incorrect Values For Whole Numbers

    Hello,

    I am having issues with the .Find function within VBA. I have two worksheets: Data and Outputs. The .Find function first matches the values in column F of the Outputs sheet with the values in column F of the Data sheet. It then returns all values from the matched row in the Data sheet to the row with the searched value in the Outputs sheet.

    I am experiencing my problem when I reach whole numbers. For example, in row 40 out the Output sheet, the function is supposed to lookup the value "3.00." When stepping through my code, however, it seems to interpret this number as "1.30." I have played around with this for a while and cannot seem to figure out what is going wrong. Attached is my workbook. Any help is greatly appreciated. Thanks!

    Steve
    Attached Files Attached Files
    A VBA novice, excited to learn!

  2. #2
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Try changing xlPart to xlWhole

  3. #3
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Thanks for the suggestion. I did previously try changing xlPart to xlWhole, but unfortunately this only creating more errors within the search.

  4. #4
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    I have tried looking at xlPart and xlValues, making sure that the whole numbers are indeed whole, retyping the whole numbers on both the Outputs and Data sheets. Does anyone have an idea? Thanks.

    Steve

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    How about just a formula?

    On sheet Outputs, select G5:U5. Paste

    =INDEX(Data!$G$5:$U$58, MATCH(Outputs!F5, Data!$F$5:$F$58), 0)

    in the formula bar, but don't press Enter. Instead, press and hold the Ctrl and Shift keys, then press Enter.

    Select all those cells and drag down.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Thanks shg. I will be doing this search to a large number of data sheets, so I was hoping to use the most efficient method. I have read that the .Find function works more quickly than arrays. However, if the .Find function is not working, I will be able to use the array.

  7. #7
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Steve,

    Change the Dim statement to
    Please Login or Register  to view this content.
    And too change to

    Please Login or Register  to view this content.
    Last edited by Charles; 06-22-2012 at 05:28 PM. Reason: added code
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  8. #8
    Registered User
    Join Date
    05-19-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Hi Steve,

    My apologies in advance for the long response. Your signature says you're "excited to learn" and it's difficult to explain the reason for the problems you're having in just a few words.

    The Range.Find Method is a better fit for matching Text than it is for Numbers.

    In this statement in your code:
    Please Login or Register  to view this content.
    ...the Method takes the "What:=" parameter, and treating that as a Text String it looks for matches in wsData.Columns(6).
    It's giving you desired results for some data... When What:= 2.25 A match is found for "2.25" in wsData.

    However, when What:= 3, the first partial match for "3" is "1.3"
    You definately don't want to use partial match for this situation - not only did it return "1.3" as a match for 3, it could have found "42.25" as the first match for 2.25 if you happened to have that dataset.

    Thallasa's suggestion to use xlWhole is an improvement, but there are still potential problems.
    When What:= 3, you no longer get a match of "1.3", but you also don't get a match between "3" and "3.00".

    Charles' suggestion attempts to address this with...
    Please Login or Register  to view this content.
    As explained above, we don't want xlPart because that will match "3.00" with "13.00" if you had that data.

    How about....
    wsData.Columns(6).Find(What:=rngCell.Text, LookIn:=xlValues, LookAt:=xlWhole)
    This is better because you'll get a match between "3.00" and "3.00" and you won't get a match between "3.00" with "13.00" if you had that data.
    So that could work, but since you need to find matches of that are numerically equal, it's not ideal to risk incorrect results by relying on the formatting of every cell in both ranges to be the same.

    Quote Originally Posted by Steve0492 View Post
    Thanks for the suggestion. I did previously try changing xlPart to xlWhole, but unfortunately this only creating more errors within the search.
    The "more errors" was probably due to this approach....
    Please Login or Register  to view this content.
    This code tests if there is a match for rngCell.Value. If there's a match, it finds the first blank row and copies values.
    If no match is found, it moves on to the next rngCell in rngSource. The problem is that the next match found will go in the next blank row, which isn't the one that has the current value of rngCell.

    This would avoid that problem....
    Please Login or Register  to view this content.
    Instead of using Find, I recommend you try a formula-based approach like shg suggested.
    Array formulas sometimes create a heavy calculation demand, but the formula shg suggested is relatively simple.

    The best way to find out which approach will be the most efficient for your purpose, is to test each option on a mockup that resembles your workbook in terms of size and complexity.

    The array formula can be entered through VBA, and if you find that it puts too much load on your calculations, you can add a step to your code that immediately converts the result of the formulas into values.

    Hope this helps!
    Last edited by JS411; 06-23-2012 at 03:40 AM.

  9. #9
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Charles and JS411,

    Thank you both for the responses. I used a combination of what you said to solve the issue. I took Charles suggestions of redefining the rngMatch variable as a Variant rather than a Range.

    Please Login or Register  to view this content.
    I also changed rngCell.Value to rngCell.Text as Charles suggested. In addition, I changed LookAt:=xlPart to LookAt:=xlWhole based off JS411's advice.

    Please Login or Register  to view this content.
    Finally, I found one additional change needed. My values were originally formatted as numbers. Once I re-formatted them to text, the code returned the correct values everytime. JS411 and shg, your advice about using a formula is well taken. I will definitely consider this approach in the future. My main concern is using up too much memory, but copying and pasting as values should avoid this. Thanks again to all who helped.

    Steve

    Here is the final code:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-19-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Hi Steve,

    As I noted earlier, Find will work if all your data is formatted consisently. IMHO, it's just not the best choice.

    On the other hand, I feel I need to try again to call your attention to the flaw in this part of your "final" code....

    Please Login or Register  to view this content.
    Do you understand that if a match isn't found, instead leaving that row blank, your code will fill in incorrect values from the next row that does have a match? All your results from that point on will shifted up one row.

  11. #11
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Hmmm I did not notice this. I am leaning towards using a different method, but I want to see this through. I'm going to work on a solution...

  12. #12
    Registered User
    Join Date
    05-19-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Something like this would work...
    Please Login or Register  to view this content.
    As a minor point, LastCol will give a larger number of columns than you need.
    If LastCol is 16 for Column "P", you really only need to copy 10 Columns since your copied values start a Column G.

  13. #13
    Forum Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: .Find Method Returning Incorrect Values For Whole Numbers

    Here code that inputs the word "ERROR" if no match is found. Thanks for catching that JS411.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1