Index function in vba not returning result

I have tested something similar and see no issues.

A =Application.WorksheetFunction.Match(papername, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Columns(1), 0)
B = Application.WorksheetFunction.Match(paperdate, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Rows(r), 0))

Do you get the correct results for both A and B?

you have entered Columns(1), I think you should be using Columns("A:A")

you have entered Rows(r), I think you should be using Columns(r & ":" & r)

You have used several range names do they all exist

I have tested something similar and see no issues.

A =Application.WorksheetFunction.Match(papername, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Columns(1), 0)
B = Application.WorksheetFunction.Match(paperdate, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Rows(r), 0))

Do you get the correct results for both A and B?

you have entered Columns(1), I think you should be using Columns("A:A")

you have entered Rows(r), I think you should be using Columns(r & ":" & r)

You have used several range names do they all exist


You use

Please Login or Register  to view this content.

Does papername exist as a public variable? You may have declared papername as a variable in another macro but you cannot use it in another macro unless it is declared as a public variable.

You use

Please Login or Register  to view this content.

Did you declare sourcerange as a named range in this module or in excel.

If in another module it should be declared as a public variable. If in excel you should be using something like this:-

Please Login or Register  to view this content.

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,

Please Login or Register  to view this content.

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.

Don't simply copy my typos. I appear to have misread your sheet name. Looking more closely, it looks like it is Feuil1. Subscript out of range means that one of those "workbook" and/or "sheet" and/or "Range" designations is misspelled or not present, and it looks like I misspelled/misread the sheet name.

I did not see this line of code in the previously posted code. The main thing I note in this statement is that the range() method is not qualified, so it will refer to whatever happens to be the currently active sheet. If you qualified your range reference (something like Set sourcerange=workbooks("desired workbook").sheets("desired sheet").range("desired range"), then it should work just fine.

Look at my previous post. Post #3

Please Login or Register  to view this content.

Now I see, "sourcerange" is a named range in the worksheet, not an object/range variable in VBA.

Hi there, sorry for the late post but i worked it out:

Please Login or Register  to view this content.

it was the match function and the date that blocked it all so i used application.match (iso worksheetfunction) and clng(date) to get the date value so it works with every date format

Thanks to you all for your help!