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.
Can anybody offer a suggestion for me? Thanks so much.
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: