hellfire45
Active Member
- Joined
- Jun 7, 2014
- Messages
- 462
So I will post code below and show where the error is happening. I have a dollar amount and all I want to do is a simple LOOKUP function against a table that contains the values and a corresponding column with words in it.
<tbody>
</tbody>
This is not all the code. IT's way too much for me to expect anybody to pick through.
Error happening on the bold lines below.
In the case below, the variable of current_discount = 73.82
If I use application.LOOKUP() then it calculates the result as a ERROR 2042
If I use application.worksheetfunction.LOOKUP() then it gives me the worksheet function 1004 runtime error.
Any ideas? Thanks so much!
UAN1 | 1.00 |
UAN2 | 2.00 |
UAN3 | 3.00 |
etc | etc |
<tbody>
</tbody>
This is not all the code. IT's way too much for me to expect anybody to pick through.
Error happening on the bold lines below.
In the case below, the variable of current_discount = 73.82
If I use application.LOOKUP() then it calculates the result as a ERROR 2042
If I use application.worksheetfunction.LOOKUP() then it gives me the worksheet function 1004 runtime error.
Any ideas? Thanks so much!
Code:
Dim UAN_UAN As Integer
Dim UAN_DISCOUNT As Integer
Dim UAN_last_row As Long
Dim UAN_match1_range As Range
Dim UAN_match2_range As Range
Dim current_discount As Variant
Dim current_UAN As Variant
Dim current_UAN_amt As Variant
UAN_UAN = Application.Match("UAN#", UAN.Rows("1:1"), 0) UAN_DISCOUNT = Application.Match("Discount Amt ($)", UAN.Rows("1:1"), 0)
UAN_last_row = UAN.Columns(UAN_UAN).Find("*", , xlValues, , xlRows, xlPrevious).Row
Set UAN_match1_range = UAN.Range(UAN.Cells(1, UAN_UAN), UAN.Cells(UAN_last_row, UAN_UAN))
Set UAN_match2_range = UAN.Range(UAN.Cells(1, UAN_DISCOUNT), UAN.Cells(UAN_last_row, UAN_DISCOUNT))
Set MAT_match1_range = MAT.Range(MAT.Cells(header_row, MAT_MTM), MAT.Cells(MAT_last_row, MAT_MTM))
Set MAT_index_range = MAT.Range(MAT.Cells(header_row, MAT_MTM), MAT.Cells(MAT_last_row, MAT_LPPRICE))
For x_Row = 2 To last_row
current_match = .Cells(x_Row, cust_po).Value
.Cells(x_Row, reseller_Col).Value = WorksheetFunction.IfError(Application.Index(cm_index_range, Application.Match(current_match, cm_match1_range, 0), 2), Application.Index(cm_index_range, Application.Match(CLng(current_match), cm_match1_range, 0), 2))
.Cells(x_Row, td_acct_col).Value = WorksheetFunction.IfError(Application.Index(cm_index_range, Application.Match(current_match, cm_match1_range, 0), 3), Application.Index(cm_index_range, Application.Match(CLng(current_match), cm_match1_range, 0), 3))
.Cells(x_Row, WOI_col).Value = WorksheetFunction.IfError(Application.Index(cm_index_range, Application.Match(current_match, cm_match1_range, 0), 4), Application.Index(cm_index_range, Application.Match(CLng(current_match), cm_match1_range, 0), 4))
current_SKU = .Cells(x_Row, sku_id).Value
.Cells(x_Row, TD_SKU_COL).Value = Application.Index(MAT_index_range, Application.Match(current_SKU, MAT_match1_range, 0), 2)
.Cells(x_Row, SKU_STATUS_COL).Value = Application.Index(MAT_index_range, Application.Match(current_SKU, MAT_match1_range, 0), 4)
.Cells(x_Row, pp_col).Value = Application.Index(MAT_index_range, Application.Match(current_SKU, MAT_match1_range, 0), 6)
.Cells(x_Row, msrp_col).Value = Application.Index(MAT_index_range, Application.Match(current_SKU, MAT_match1_range, 0), 7)
.Cells(x_Row, amount_col).Value = .Cells(x_Row, pp_col) - .Cells(x_Row, unit_price)
current_discount = .Cells(x_Row, amount_col).Value
If current_discount > 0 Then
Do Until current_discount = 0
[B] current_UAN = Application.WorksheetFunction.Lookup(current_discount, UAN_match2_range, UAN_match1_range)[/B]
[B] current_UAN_amt = Application.Lookup(current_discount, UAN_match2_range, UAN_match2_range)[/B]
For x_uan = uan1 To uan6
.Cells(x_Row, x_uan).Value = current_UAN
Next
current_discount = current_discount - current_UAN_amt
Loop
End If
Next