I am trying to take a date from a Userform field and compare it to Column "A". If there is a match, I want the row number to the row where the match was found.
For testing purposes these are the values in Column "A":
[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD]#2/26/2018#[/TD]
[/TR]
[TR]
[TD]#2/25/2018#[/TD]
[/TR]
[TR]
[TD]#2/21/2018#[/TD]
[/TR]
[TR]
[TD]#2/22/2018#[/TD]
[/TR]
[TR]
[TD]#2/28/2018#
[/TD]
[/TR]
</tbody>[/TABLE]
I want to use Serial Dates to avoid potential issues of date formats. The problem I am getting is that I want to use Match with Type 0 to get an exact match. No matter what I do, I get an Error 2042. This is the code I have so far:
Dim wbname As String
Dim DT As Date
DT = CDate(txtDate)
wbname = ThisWorkbook.Name
RowCount = Workbooks(wbname).Worksheets("Course Data").UsedRange.Rows.Count
ColCount = Workbooks(wbname).Worksheets("Course Data").UsedRange.Columns.Count
ColLett = Split(Cells(1, ColCount).Address, "$")(1)
RangeName = "ScheduleData"
CellName = "A1:" & ColLett & RowCount + 1
Set cell = Workbooks(wbname).Worksheets("Course Data").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
ColLett = Split(Cells(1, 1).Address, "$")(1)
RangeName = "Dates"
CellName = "A1:" & ColLett & RowCount + 1
Set cell = Workbooks(wbname).Worksheets("Course Data").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
Set Dates = Workbooks(wbname).Names("Dates").RefersToRange
Set ScheduleData = Workbooks(wbname).Names("ScheduleData").RefersToRange
Variable = Application.Match(DT, Dates, 0)
For testing purposes these are the values in Column "A":
[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD]#2/26/2018#[/TD]
[/TR]
[TR]
[TD]#2/25/2018#[/TD]
[/TR]
[TR]
[TD]#2/21/2018#[/TD]
[/TR]
[TR]
[TD]#2/22/2018#[/TD]
[/TR]
[TR]
[TD]#2/28/2018#
[/TD]
[/TR]
</tbody>[/TABLE]
I want to use Serial Dates to avoid potential issues of date formats. The problem I am getting is that I want to use Match with Type 0 to get an exact match. No matter what I do, I get an Error 2042. This is the code I have so far:
Dim wbname As String
Dim DT As Date
DT = CDate(txtDate)
wbname = ThisWorkbook.Name
RowCount = Workbooks(wbname).Worksheets("Course Data").UsedRange.Rows.Count
ColCount = Workbooks(wbname).Worksheets("Course Data").UsedRange.Columns.Count
ColLett = Split(Cells(1, ColCount).Address, "$")(1)
RangeName = "ScheduleData"
CellName = "A1:" & ColLett & RowCount + 1
Set cell = Workbooks(wbname).Worksheets("Course Data").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
ColLett = Split(Cells(1, 1).Address, "$")(1)
RangeName = "Dates"
CellName = "A1:" & ColLett & RowCount + 1
Set cell = Workbooks(wbname).Worksheets("Course Data").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
Set Dates = Workbooks(wbname).Names("Dates").RefersToRange
Set ScheduleData = Workbooks(wbname).Names("ScheduleData").RefersToRange
Variable = Application.Match(DT, Dates, 0)