Custom Format use with MATCH function

[Enz]

Hello,

I am coding a macro that accepts an input file with a date in Custom
format "dd-mmm". Example of content is "01/10/2008" which appears as
"01-Oct". Within the macro I have tab with columns labelled with
Custom Format "mmm-yyyy", example "Oct-2008", "Nov-2008" that I would
like to populate with data from the input file.

I have tried to convert the input date in several ways into for
example into "Oct-2008", then using the MATCH(convertedDate, range
within the sheet, 0), to find the column to populate with other input
data.

This unfortunately always produced the "error 2042" error message. I
have checked and the input file date is in date format, and I have
double checked the columns to ensure they are in a Custom Format also
that is a date, and they are.

Is there a better/more efficient way to perform this?

This is a version I have here does not work as lMonthYear2 is not
converting properly, but I am more interested in finding the best way
to do the search I am interested in doing based on the nature of the
input data.

lMonthYear = CDate(WWExtractInput.Worksheets(1).Cells(2, 6).Value)
lMonthYear2 = Format(lMonthYear, "mmm-yyyy")
lTeamYTDColumnTemp = Application.Match(lMonthYear2,
wsTeamYTDTab.Range("D22"), 0)

Your assistance is much appreciated.

regards,
Enzo

 

[Joel]

You don't need to convert to a string for the match function. You should be
using the serial date. You may have problems with matching if the date
(Oct-2008) is not the first of the month. When you are displaying Oct-2008
it can be any day of the month

First try this. Note CDate shouldn't be required unless you have a sttring

lMonthYear = WWExtractInput.Worksheets(1).Cells(2, 6).Value
lTeamYTDColumnTemp = _
Application.Match(lMonthYear,wsTeamYTDTab.Range("D22"), 0)

Second try this

lMonthYear = WWExtractInput.Worksheets(1).Cells(2, 6).Value
lMonthYear2 = DateSerial(year(lMonthYear),month(lMonthYear),1)
lTeamYTDColumnTemp = _
Application.Match(lMonthYear2,wsTeamYTDTab.Range("D22"), 0)

If neither works then the dates in D22 and not serial dates or not the 1st
of the month and should be fixed.