Index function in vba not returning result

[nikedelik23]

Hi all,

I want an Index / Match function to return a result from another workbook but no matter the way i try my variable "orderamount" still returns nothing:

'get order amount in jx2015

Workbooks("Journaux_2015.xls").Activate
Sheets("Feuil1").Activate
            
orderamount = Application.WorksheetFunction.Index(Workbooks("Journaux_2015.xls").Sheets("Feuil1").sourcerange, _
Application.WorksheetFunction.Match(papername, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Columns(1), 0), _
Application.WorksheetFunction.Match(paperdate, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Rows(r), 0))

I am using variables in the match functions, one is as string and the other is as date, could that be the problem?

Thanks in advance for your help.

[mehmetcik]

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

[nikedelik23]

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

Hi mehmetcik and thanks for your post,

i tried to run each match function alone but I get the message: "unable to get the match property of the worksheet function class" :

Sub test1()

Dim a, b As Long
dim r As integer
Dim papername As String
Dim paperdate As Date

r = 3

papername = "Le Matin"
paperdate = "01/01/2015"

Workbooks("Journaux_2015.xls").Sheets("Feuil1").Activate

a = Application.WorksheetFunction.Match(papername, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Columns("A:A"), 0)

MsgBox a

b = Application.WorksheetFunction.Match(paperdate, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Rows(r & ":" & r), 0)

MsgBox b

End Sub

[mehmetcik]

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

Application.WorksheetFunction.Match(papername, Workbooks("Journaux_2015.xls").Sheets("Feuil1").Columns(1), 0)

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

Application.WorksheetFunction.Index(Workbooks("Journaux_2015.xls").Sheets("Feuil1").sourcerange

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:-

Application.WorksheetFunction.Index(Workbooks("Journaux_2015.xls").Sheets("Feuil1").range("sourcerange")

[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.

[nikedelik23]

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" !??

[nikedelik23]

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?

[MrShorty]

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.

[nikedelik23]

I did not copy/paste your tread, it is really this range variable that i do not set properly i guess:

Set sourcerange = Range(Cells(f, 1), Cells(64, 243))

[MrShorty]

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.

[mehmetcik]

Look at my previous post. Post #3

Application.WorksheetFunction.Index(Workbooks("Journaux_2015.xls").Sheets("Feuil1").range("sourcerange")

[MrShorty]

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

[nikedelik23]

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

 orderamount = Application.WorksheetFunction.Index(sourcerange, _
                        Application.Match(papername, paperrange, 0), _
                        Application.Match(CLng(paperdate), amountrange, 0))

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!