VBA Match problem

[liampog]

Hi

I have a master workbook that opens another workbook. On the opened workbook, there is a date.

I want to find the column in the master workbook that contains this date and I'm having an issue with the following line of code:

Week_Column = Application.WorksheetFunction.Match(WeekEndingDate, Workbooks(Report_Filename).Sheets("Sheet1").Range("Setup_DatesList"), 0)

Report_Filename is the master workbook. WeekEndingDate is taken from the opened workbook. It's actually a date extracted from a text string so I did this:

WeekEndingDate = DateValue(Mid(FindText, Application.WorksheetFunction.Find(",", FindText) - 10, 10))

I believe DateValue is the correct thing to use to turn the text version of the date into a real date value.

The Range("Setup_DatesList") is a named range of dates that are derived from formulas rather than actual input, but I don't believe this should be causing the problem.

I'm getting the standard runtime error 1004, unable to get the Match property of the WorksheetFunction class.

Can anyone tell me what I'm doing wrong?

Thanks
Liam

 

[Akuini]

Untested but maybe something like this:
Use 'Application.Match'.

Dim Week_Column As Variant
Week_Column = Application.Match(WeekEndingDate, Workbooks(Report_Filename).Sheets("Sheet1").Range("Setup_DatesList"), 0)

If IsNumeric(Week_Column) Then
'do something here if match found
End If

 

[liampog]

Untested but maybe something like this:
Use 'Application.Match'.

Dim Week_Column As Variant
Week_Column = Application.Match(WeekEndingDate, Workbooks(Report_Filename).Sheets("Sheet1").Range("Setup_DatesList"), 0)

If IsNumeric(Week_Column) Then
'do something here if match found
End If

Hi

That doesn't work unfortunately. It creates a Type mismatch error message.

Could it be to do with the date values in the Range("Setup_DatesList") being as a result of formulas rather than actual dates?

Am I correct in using DateValue to convert a date (in dd/mm/yyyy format) extracted from a text string to an actual date numerical value to use in the Match formula?



Thanks
Liam

 

[Akuini]

That doesn't work unfortunately.  It creates a Type mismatch error message.

Did you Dim Week_Column As Variant or Long?
It should be variant.

 

[liampog]

Hi

I actually did some more digging and found a reference that says it's better to use cLng() around dates.

So the following works!

Week_Column = Application.Match(CLng(WeekEndingDate), Workbooks(Report_Filename).Sheets("Sheet1").Range("Setup_Dates"), 0)

Thanks for your help though.

 

[Akuini]

Ok, glad you figured it out.