VBA ERROR - LOOKUP Function Runtime 1004

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
447
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.

UAN11.00
UAN22.00
UAN33.00
etcetc

<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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top