Match Failing When It Shouldn't

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

The code without the sheet could make debugging this difficult. I note that your MATCH() function is an "exact" match -- meaning that the date in rnglo must match the corresponding entry in the spreadsheet exactly (and Excel -- like many things computers do -- can get annoyingly technical about what exactly means).

In my quick mockup, the lookup failed and I think it has to do with data types. A spreadsheet only knows two data types -- double floating point or text/string. VBA, of course, can recognize many more data types. Not knowing anything about the internal workings of Excel's MATCH() function (designed to work mostly with spreadsheet data -- so doubles or string), my guess is that something about the type conversion (or lack of type conversion) causes the data type between the value in rnglo passed to the match function and the double precision or text values in K2:K21 is causing the MATCH() function to fail to find the match.

Knowing that the spreadsheet only recognizes double or string, I tried assigning rnglo a double:

Please Login or Register  to view this content.

-- knowing that the dates in my test sheet were true date/time serial numbers -- so they are stored as doubles. That seemed to allow the match function to locate the correct entry. My suggestion would be to avoid some of the ways that data types might mismatch by making sure that your values in the spreadsheet (whether double or text) and VBA are of the same data type.

If you want us to get serious about debugging your spreadsheet, then do as TMS suggests and upload an actual workbook (dummy data, as necessary) for us to look at.

1/7/2019 may exist on your spreadsheet. but if you format the cell as general does it still look like 1/7/2019 or does it look like 43647

If 1/7/2019 then check if your data has spaces before or after

if 43647 then use date value to convert you search string into a number.