.Find Method Returning Incorrect Values For Whole Numbers

[Steve0492]

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

[Thalassa]

Try changing xlPart to xlWhole

[Steve0492]

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

[Steve0492]

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

[shg]

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.

[Steve0492]

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.

[Charles]

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.

[JS411]

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.

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!

[Steve0492]

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.

[JS411]

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.

[Steve0492]

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...

[JS411]

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.

[Steve0492]

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.