Index function in vba not returning result

Originally Posted by MrShorty

Do you know which Match function is failing? If you step through the code (http://www.cpearson.com/Excel/DebuggingVBA.aspx ), which line causes the error?

Usually this error is because the exact text string (3rd argument to the Match function =0) "Le Matin" is not found in .Columns("A:A") or the exact date value "01/01/2015" is not found in row r. That would be my first guess. This will mean looking through those ranges and at those values and determining if they are there or not. Look for leading/trailing spaces or other non-printing characters in your text strings, and make sure the date search is not missing something because of date/time combinations.

As a debugging step, you might try pulling values from within the search range. For example,

 papername=workbooks("Journaux_2015.xls").sheets("Feuill").range("A10").value
paperdate=workbooks("Journaux_2015.xls").sheets("Feuill").range("M3").value

so that you know your search values are taken from the database, and should be found. That will give you the opportunity to compare what these variables contain when something known to be in the database is there, and what is there when you enter the constant values that you currently have.

when running:

papername = Workbooks("Journaux_2015.xls").Sheets("Feuill").Range("d7").Value

I get "subscript out of range" !??

Ok i sorted out the match problem using activesheet method but the problem is is my range variable: sourcerange

the worksheet function does not support this property apparently, any idea?