Unable to Get Match Property of worksheetfunction class

[mangeshmm]

This is driving me nuts and Im hoping that someone knows what is going on. I have a simple 1 col table on sheet 1 with a list of dates. On sheet 2 of the same workbook, I have a table with 10 rows which contains end of month dates from 30 Nov 2017 to 31 Aug 2018 (30 Nov 2017, 31 Dec 2017 .... 31 Aug 2018).

Through VBA, I am converting the dates on the table on sheet1 through a worksheetfunction.eomonth formula. Later in the same code, I use the worksheetfunction.match formula to match the EOMonth dates to the dates on sheet2. However, for reasons totally beyond me, I get the unable to get match error.

Now here is the freaky part - if I abandon VBA and mimic the code using the same spreadsheet formulas - there is no problem in getting a Match.

What the hell is going on here?????????

 

[Kenneth Hobson]

What is your VBA code? Have you tried CStr()? e.g.

'If array is 0 based, 1 is returned if aValue matches anArray(0).
Function PosInArray(aValue, anArray)
  Dim pos As Long
  On Error Resume Next
  pos = -1
  pos = WorksheetFunction.Match(CStr(aValue), anArray, 0)
  PosInArray = pos
End Function

 

[mangeshmm]

The problem is that both tables in questions have dates. To be clear, table 1 has dates spread throughout the month (1-Nov-17, 23-Dec-17, 13-Feb-18, 5-May-18, etc). On table 2, I have just 10 rows with the following dates 30-Nov-17, 31-Dec-17, 31-Jan-18, 28-Feb-18, 31-Mar-18, 30-Apr-18, 31-May-18, 30-Jun-18, 31-Jul-18, 31-Aug-18.

What I am doing through my code is changing the dates on table 1 to the EOMonth. So that the above dates would become (30-Nov-17, 31-Dec-17, 28-Feb-18, 31-May-18). Once the code converts each date on table1 through worksheetfunction.eomonth, it seeks to match this date with the dates on table2. So there is no reason why the match would return an error.

And yet it does.....................................................