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