Mysterious Failing MATCH statement (VBA)

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
462
I have a frustrating issue. In dire need of an assist. I have a worksheet in which the second row (the range of which is comsheet_index1_row range) contains values of Sundays. Formatted as mm/dd/yyyy.

The value that is getting an error 2042 is the last line seen below. It is trying to match current_order_week (which is 11/27/2016) to a range in which 11/27/2016 is located in column 3.

I cannot understand why it cannot find this value. The Sundays in the worksheet are definitely dates. And are created using the following formula.

Code:
=IFERROR(IF((FI2+7)>MAX(Demand!$G:$G),"",(FI2+7)),"")

Can anybody offer a suggestion for me? Thanks so much.

Code:
Dim Current_order_week As Variant
Dim last_sunday As Date
Dim date_week As String
Dim date_year As String
Dim forced_commit As Integer
Dim forced_commit_extended As Integer
Dim index1value As Long
Dim index2value As Long

Dim matchtest1 As Variant
Dim com_bin_Start As Long
Dim psd_allocation_start As Long
Dim com_last As Long


Dim comsheet_index1_range As Range
Dim comsheet_index1_row_range As Range
Dim comsheet_index1_Col_Range As Range
Dim comsheet_index2_range As Range
Dim comsheet_index2_row_range As Range
Dim comsheet_index2_Col_Range As Range

'ranges for SELECT CASE 2
                        com_bin_Start = Application.Match("Bin/MAP Allocation to Forecast -------->", COM.Rows("1:1"), 0) - 1
                        psd_allocation_start = Application.Match("PSD Allocation ----->", COM.Rows("1:1"), 0) - 2
                        forecastdemand_Start = Application.Match("Forecasted Demand Receipts ------->", COM.Rows("1:1"), 0) - 1
                        com_last = COM.Columns(com_bin_Start).Find("*", , , , xlRows, xlPrevious).Row
                        
                        'index 1 ranges
                        Set comsheet_index1_range = COM.Range(COM.Cells(2, com_bin_Start), COM.Cells(com_last, psd_allocation_start))
                        Set comsheet_index1_row_range = COM.Range(COM.Cells(2, com_bin_Start), COM.Cells(2, psd_allocation_start))
                        Set comsheet_index1_Col_Range = COM.Range(COM.Cells(2, com_bin_Start), COM.Cells(com_last, com_bin_Start))
                        
                        'index 2 ranges
                        Set comsheet_index2_range = COM.Range(COM.Cells(2, psd_allocation_start), COM.Cells(com_last, forecastdemand_Start))
                        Set comsheet_index2_row_range = COM.Range(COM.Cells(2, psd_allocation_start), COM.Cells(2, forecastdemand_Start))
                        Set comsheet_index2_Col_Range = COM.Range(COM.Cells(2, psd_allocation_start), COM.Cells(com_last, psd_allocation_start))
            

Select Case Current_formula
                                                            Case "Tech Data Commit"
                                                                        last_sunday = Date - Weekday(Date) + 1
                                                                        forced_commit = ref.Range("L22").value
                                                                        forced_commit_extended = ref.Range("L23").value
                                                                        matchtest1 = Application.Match(Current_order_week, comsheet_index1_row_range, 0)
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I assume you've snipped out the code where you assign Current_order_week a value?

I would suggest converting to a number and using:
Code:
matchtest1 = Application.Match(CDbl(Current_order_week), comsheet_index1_row_range, 0)
 
Upvote 0
I assume you've snipped out the code where you assign Current_order_week a value?

I would suggest converting to a number and using:
Code:
matchtest1 = Application.Match(CDbl(Current_order_week), comsheet_index1_row_range, 0)

Sorry
Yes, this VBA loop is like 15 pages long. Here is the line of code where Current_order_week is assigned a value. at the bottom

Code:
With LCPFR                        For MY_COL = first_loop_col To last_loop_col
                                    For My_row = first_loop_row + 1 To last_loop_row
                                                Current_formula = .Cells(My_row, CASE_SELECT_LOCK)
                                                Current_MTM = .Cells(My_row, MTM_col)
                                                Current_Brand = .Cells(My_row, brand_col)
                                                Current_Customer = .Range("B1").value
                                                Current_order_week = .Cells(1, MY_COL)
 
Upvote 0
I assume you've snipped out the code where you assign Current_order_week a value?

I would suggest converting to a number and using:
Code:
matchtest1 = Application.Match(CDbl(Current_order_week), comsheet_index1_row_range, 0)


This
seems to have fixed it. Can you please explain how you did it? I don't understand why the solution works the way it does. Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top