VBA index and match function
- Thread starter shafiq2
- Start date
NARAYANK991
Excel Ninja
Hi ,
By not specifying your problem with the exact formula you have used ( right down to the cell references ) , all you will get are general answers which might or might not solve your problem.
If you want a precise answer , give a specific question. Post the exact formula you have used , preferably copying it from your worksheet and pasting it.
Narayan
By not specifying your problem with the exact formula you have used ( right down to the cell references ) , all you will get are general answers which might or might not solve your problem.
If you want a precise answer , give a specific question. Post the exact formula you have used , preferably copying it from your worksheet and pasting it.
Narayan
Hi,
Here is the code I am using, and the code gives error on this line
Selection.Formula = Application.WorksheetFunction.Index(myrange, Application.WorksheetFunction.Match(myrange2, myrange3, 0))
Here is the code I am using, and the code gives error on this line
Selection.Formula = Application.WorksheetFunction.Index(myrange, Application.WorksheetFunction.Match(myrange2, myrange3, 0))
Code:
Option Explicit
Sub indexANDmatch_Assistant()
Dim myrange As range
Dim selected As String
Dim I As String
Dim lastrow As String
Dim myrange2 As range
Dim answer As String
Dim myrange3 As range
Dim myrange4 As range
Dim cell As range
Dim myrange5 As String
Dim range As range
Dim MYRANGE7 As range
Dim MYRANGE6 As range
Dim tes As range
Set myrange = Application.InputBox("Select Index range", "Select Range", Type:=8)
'MYRANGE7 = range(myrange & Rows.Count).End(xlUp).Row
If myrange Is Nothing Then
'range is blank
Else
myrange.Select
End If
'answer = MsgBox("Do you want to select the entire used rows", vbYesNo, "Range Selection")
'If answer = vbYes Then
'Range(Selection, Selection.End(xlDown)).Select
Set myrange2 = Application.InputBox("Select Match Type", "Select Array", Type:=8)
If myrange2 Is Nothing Then
'range is blank
Else
myrange2.Select
End If
Set myrange3 = Application.InputBox("Select Match Array", "Select Range", Type:=8)
If myrange3 Is Nothing Then
'range is blank
Else
myrange3.Select
'range(Selection, Selection.End(xlDown)).Select
End If
myrange5 = Application.InputBox("Select Text", "Wrtie Text", Type:=0)
Set myrange4 = Application.InputBox("Select Result range", "Select Range", Type:=8)
If myrange4 Is Nothing Then
'range is blank
Else
myrange4.Select
'range(Selection, Selection.End(xlDown)).Select
Selection.Formula = Application.WorksheetFunction.Index(myrange, Application.WorksheetFunction.Match(myrange2, myrange3, 0))
End If
'If answer = vbNo Then
'NOTHING
'End If
End SubNARAYANK991
Excel Ninja
Hi Shafiq ,
Depending on what you want to do , there is nothing wrong with the code ; it is all a matter of how it is being put to use.
The statement which is giving an error has 3 range references :
myrange
myrange2
myrange3
myrange2 is the lookup reference for the MATCH function ; I assume this will be a single-cell reference , and it will contain data which will be looked up in the range myrange3.
Since myrange3 is the lookup array for the MATCH function , it has to be either a single row or a single column range. Since this is a user input , it is not a good idea for it to be coded like this , since if the user enters a multiple row multiple column range reference , it will generate an error.
Now , it is a matter of myrange ; if you wish , it can be a single row / single column range reference , different from myrange3 , or it can be a multiple row , multiple column range reference. If it is the former , nothing needs to be done ; the code will work as it is.
If it is the latter , then you need to give a column index , since the MATCH function will only return a row index ; if this is not given , then again the INDEX function will generate an error.
Thus , the result entirely depends on what the user inputs ; given the proper inputs , the statement should work as it is , otherwise , to make it work , you will need to provide a column index ; but this is entirely dependent on how you have your data laid out. None of us can say what it should be.
In the attached file , the user responses to the prompts are as follows :
A1:F15
H1
C1:C15
irrelevant , since this input is not made use of
J10 : P20
Narayan
Depending on what you want to do , there is nothing wrong with the code ; it is all a matter of how it is being put to use.
The statement which is giving an error has 3 range references :
myrange
myrange2
myrange3
myrange2 is the lookup reference for the MATCH function ; I assume this will be a single-cell reference , and it will contain data which will be looked up in the range myrange3.
Since myrange3 is the lookup array for the MATCH function , it has to be either a single row or a single column range. Since this is a user input , it is not a good idea for it to be coded like this , since if the user enters a multiple row multiple column range reference , it will generate an error.
Now , it is a matter of myrange ; if you wish , it can be a single row / single column range reference , different from myrange3 , or it can be a multiple row , multiple column range reference. If it is the former , nothing needs to be done ; the code will work as it is.
If it is the latter , then you need to give a column index , since the MATCH function will only return a row index ; if this is not given , then again the INDEX function will generate an error.
Thus , the result entirely depends on what the user inputs ; given the proper inputs , the statement should work as it is , otherwise , to make it work , you will need to provide a column index ; but this is entirely dependent on how you have your data laid out. None of us can say what it should be.
In the attached file , the user responses to the prompts are as follows :
A1:F15
H1
C1:C15
irrelevant , since this input is not made use of
J10 : P20
Narayan
Last edited:
Hi Narayan,
Thanks for detail explanation, actually the problem is that range "Myrange2" which is the lookup reference is not always a single cell reference but one column reference. If I select one cell reference yes the code works perfectly. could you please help how to make this code work with selection of entire column as lookup reference. or to initially select single cell reference and then move to the next cell within the same column.
Regards,
Shafiq
Thanks for detail explanation, actually the problem is that range "Myrange2" which is the lookup reference is not always a single cell reference but one column reference. If I select one cell reference yes the code works perfectly. could you please help how to make this code work with selection of entire column as lookup reference. or to initially select single cell reference and then move to the next cell within the same column.
Regards,
Shafiq
NARAYANK991
Excel Ninja
Hi Shafiq ,
I am sorry , but I am short of time ; hopefully someone else will help.
Narayan
I am sorry , but I am short of time ; hopefully someone else will help.
Narayan
Somendra Misra
Excel Ninja
Hi Shafiq,
If you can upload a sample file with the code and some data and try to explain your problem on that file, may be somebody will help you.
Regards,
If you can upload a sample file with the code and some data and try to explain your problem on that file, may be somebody will help you.
Regards,
Hi,
I have written vba code for index and match where user can select the required range in inputbox, the code is perfectly fine and it works if the selected match value is a single cell, however I need help to make this code work for the entire column to match each cell one by one and then move to the next cell.
Attached please find a sample file with the code.
Thank you for your help.
Best Regards,
Shafiq
I have written vba code for index and match where user can select the required range in inputbox, the code is perfectly fine and it works if the selected match value is a single cell, however I need help to make this code work for the entire column to match each cell one by one and then move to the next cell.
Attached please find a sample file with the code.
Thank you for your help.
Best Regards,
Shafiq
Somendra Misra
Excel Ninja
@shafiq2
All I can judge that when you pass a range of value to match, some values will match and some not and those values that will not match will return a #n/a error which I think VBA is not able to handle and thus return a error.
I will suggest you not to match entire range in once but MATCH one by one. See the file.
Regards,
All I can judge that when you pass a range of value to match, some values will match and some not and those values that will not match will return a #n/a error which I think VBA is not able to handle and thus return a error.
I will suggest you not to match entire range in once but MATCH one by one. See the file.
Regards,
Somendra Misra
Excel Ninja
Hi Marc,
I think you have not seen the code I had given in my file :
It use IF NOT ISERROR(Application.Match
I had not used on Error statement.
Regards,
I think you have not seen the code I had given in my file :
It use IF NOT ISERROR(Application.Match
I had not used on Error statement.
Regards,
Marc L
Excel Ninja
Yes, I can't see your code.
It's just a thought in response to your post #11,
sorry if I misunderstood it …
In case of not matching, worksheetfunction needs On Error,
the way at Application level with a Variant variable not,
the reason I wrote that …
Of course if you had embedded your code between tags in your post,
I never wrote anything …
Last edited: