PDA

View Full Version : Find problem



jwise
01-28-2011, 01:36 PM
Hi,

I wrote a macro and function to 'lookup' the PO number of a purchase. The lookup is done with the amount. There are some amounts which do not have a match; I put '*** N/A ***' in that case.

The code works fine unless the amount ends in zero, i.e. the amount is evenly divisible by 10 cents. For example, 1002.20 does not work but 927.01 does.

I have tested this code with lots of data. The spreadsheet fields must be defined EXACTLY or other no-matches occur. I thought that 'Format' didn't influence the way data was stored, but it seems it must. Any ideas?

I'm using Excel 2003.

The error is in the function 'PO_find' and I'll list this code here.

[INDENT] Function PO_find(CN_sheet As Worksheet, _
Inv_Amt As Double) As String
'
'********************************************************
'
' Adapted from Abbrev
'
'
'*********************************************************
'

Dim PO_Inv As String

On Error Resume Next

PO_Inv = "*** N/A ***"

With Range(CN_sheet.Columns(5), CN_sheet.Columns(9))

PO_Inv = .Find(What:=Inv_Amt, _
After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, 4)

End With

On Error GoTo 0

PO_find = PO_Inv

End Function

I'll attach the workbook so you can see valid data. The macro is run against sheet 'Cash' and it uses sheet 'PC' to do the lookup. To use the macro, first select the 'payment amount' (column 'E' in Cash) and invoke 'Get_PO'.

The results are placed in column 'J' of the 'Cash' worksheet, overlaying anything there. The correct range to be selected is 'E5:E56'. The values currently in column 'J' are from my last test.

I thought this problem might be related to 'xlWhole' or 'xlValues'; unfortunately, I'm unable to navigate through the MS documentation to their exact meaning.

mbarron
01-28-2011, 05:48 PM
I thought this problem might be related to 'xlWhole'
You are correct- it is related to the xlWhole. Using the value of 8019.6o for example. 8019.60 which is actually passed to your function as 8019.6. Using xlWhole in the Find function force the function to look for what is actually displayed in the cell. Since 8019.60 is in the cell, there is no match. Change the xlWhole to xlPart and it should work.

mbarron
01-28-2011, 06:37 PM
Another option would be to use the VLOOKUP function in your function:


Function PO_find(CN_sheet As Worksheet, _
Inv_Amt As Double) As String
'
'********************************************************
'
' Adapted from Abbrev
'
'
'*********************************************************
'

Dim PO_Inv As String

On Error Resume Next

PO_Inv = "*** N/A ***"

PO_Inv = Application.WorksheetFunction.VLookup(Inv_Amt, _
Range(CN_sheet.Columns(5), CN_sheet.Columns(9)), 5, 0)


On Error GoTo 0

PO_find = PO_Inv

End Function

macropod
01-28-2011, 08:20 PM
Another option would be to use the VLOOKUP function in your functionIndeed, I wonder why you'd bother with a UDF that does little more than run a built-in function.

jwise
01-29-2011, 07:30 AM
MBarron,

Thanks for your suggestion. When I used 'xlPart', instead of 'xlWhole', the macro worked much better. It still has a problem with the only 'even dollar' amount in the data, but it works for everything else.

I am disturbed that the VBE would remove the cents portion of my test code (If I entered 8019.60, VBE would change this to 8019.6). I am also disturbed that 'Format' specification in the worksheet would affect the results. This conflicts with my understanding of the way data is stored. The display of floating point data is often changed based on the number of significant digits required, but the data itself should be the same: 85.00 as a floating point number should be the same as 85 or 85.0 or 85.00000, etc. If this is wrong, then I can see why my code didn't work. Perhaps this concept doesn't carry over from my former world to the PC realm? Is the compare made against the data item or its displayed image? I had previously discovered that all my compares failed against numbers > 999.99; the data format in one of the worksheets used a comma as the thousands separator. When I removed the comma, they started working.

I will test your second suggestion. Thanks for that as well.

Paul,

The code was written in a function in an attempt to be more modular... another hangover from my former world. It allowed me to write a very simple driver routine to test the fragment, and also makes using bits and pieces of code in multiple applications easier. I seem to do a lot of that. In fact, the original routine came from another application I wrote. I suppose whether this is a good idea or not depends on how efficient subroutine linkage is in this domain.

Thanks for your suggestions.

jwise
01-29-2011, 07:43 AM
MBarron,

When I tried your alternative use of VLookup, it worked. It handles the even dollar amount.

I wish I understood why data representation affected the compares.


Thanks again.

Jwise

macropod
01-29-2011, 03:09 PM
Hi Jwise,

The difference between 'xlPart' and 'xlWhole' in this case comes about because, in string comparisons (which is what Find uses to differentiate 'xlPart' from 'xlWhole'), '8019.60' is not the same as '8019.6'.

Re:
The code was written in a function in an attempt to be more modular... I suppose whether this is a good idea or not depends on how efficient subroutine linkage is in this domain.Using Excel's native functions directly is always more efficient than wrapping them in vba.

jwise
02-01-2011, 09:59 AM
Paul,

Thank your for explaining that a string comparison is made. It makes sense that '8019.6' is not '8019.60'.

Do you know why a string compare would be used when data items are defined as 'Double'? This seems counter-intuitive to me. I realize VBA is interpreted and not compiled, but every compiler I've ever worked with used a compare based on its symbol table, i.e. a 'Double' declaration would result in a 'Double' compare.

In fact, some teach not to do compares on floating point numbers because of precision considerations. Instead you subtract the numbers you think should be equal, and test the difference to be less than a particular value. For example, if you were comparing money (two decimal places to the right), the difference might be compared to say '.002'. Any difference less than this value would mean the numbers were equal.