• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help for Index Match VBA code

N

nao

New Member
Hello,

i write the code (1) of the below Index Match formula in a Macros but it keep giving me and error matching can you please help me.

(1)=> INDEX(Table1[Taux],MATCH(F16,Table1[Category],0))

Macros => x = Application.WorksheetFunction.Index("Table1[taux]", Application.WorksheetFunction.Match("sheet4!f16", "Table1[Category]"), 0)

FYI my Table in sheet2 A1:D7

Thank you
 
@nao

First! Welcome on the forum.


You need to understand how table ranges are referred in VBA.

Standard ways are as follow:

Sheets(1).Range("Table1")

Sheets(1).Range("Table1[Qty]")

Sheets("ABCD").Range("Table1[Qty]")

Sheets("ABCD").ListObjects("Table1")

Now!!

Index + Match formula would be = index(rng,match(lookfor,rng,0))

So your formula should be

x = Application.WorksheetFunction.Index("Table1[taux]", Application.WorksheetFunction.Match("sheet4!f16", "Table1[Category]", 0))

& After taking note in correction with table range & some alteration it would be!!

x = Application.Index(range("Table1[taux]"), Application.Match(range("sheet4!f16"), range("Table1[Category]"), 0))


Check this & lets us know!!

If may throw a error if the match function did get the value.
 
Hi deepak,

i would like your help for the below:

i have two combobox, the first one i fill it from my excel as per below:

Code: 
Private Sub UserForm_Initialize()
    With Worksheets("Sheet6")
       Combobox1.List = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
End Sub

and for the second combobox i want to fill it based on my chose on the first combobox1, exemple if i choose value number 3 (in line number 3) in combobox1 i wana see in combobox2 a range of value from my excel.

Code: 
Private Sub CBxProdsub_Change()
'If me.combobox1.Value = "XXXXXX" Then
'Combobox2.Value = Sheet7.Range("a1:A7")
'End If
End Sub

And it's not working the combobox2 its still empty.
thank you very much
Nao
 
Last edited by a moderator:
thanks again for your help, i used the last link that you provide me with the user form but i have an error on the line(
Code: 
With Combobox2.Value = Feuil1.Range("A1:A5").Value
) because i have my data list on the excel. please

Code: 
PrivateSub ComboBox1_Change()
Dim index As Integer
index = Combobox1.ListIndex
Me.Combobox2.Clear
Select Case index
Case Is = 0
With Combobox2.Value = Sheet1.Range("A1:A5").Value
End With
Case Is = 1
With Combobox2.Value = Sheet2.Range("B1:B5").Value
End With
Case Is = 3
With Combobox2.Value = Sheet3.Range("C1:C5").Value
End With
End Select
 
Check this

Code: 
PrivateSub ComboBox1_Change()
Dim index AsInteger
With me.Combobox2
index = me.Combobox1.ListIndex
.Clear
SelectCase index
CaseIs = 0
.list= Sheet1.Range("A1:A5").Value
CaseIs = 1
.list= Sheet2.Range("B1:B5").Value
CaseIs = 3
.list = Sheet3.Range("C1:C5").Value
Case else
.Value=null

EndSelect
End with
End sub
 
You must log in or register to reply here.
Back
Top