Using Index function in userform

[NDMDRB]

Hello,

Please I need your help

I'm trying to find a value reference to two columns, I'v tried it in Excel worksheet, it works fine, but I'm not able to use it in VBA

Here is the function that I tested in Excel worksheet in range (I2) and works fine

=INDEX(E:E,MATCH(1,(A:A=G2)*(B:B=H2),0))


[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Inv. #[/TD]
[TD]Num[/TD]
[TD]BarCode[/TD]
[TD]Name[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD]Inv. #[/TD]
[TD]Num[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]1[/TD]
[TD]111[/TD]
[TD]Test1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]102[/TD]
[TD]3[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]2[/TD]
[TD]222[/TD]
[TD]Test2[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]111[/TD]
[TD]Test1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]102[/TD]
[TD]2[/TD]
[TD]222[/TD]
[TD]Test2[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]102[/TD]
[TD]3[/TD]
[TD]333[/TD]
[TD]Test3[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried to use it in my userform but I get an error

Here is the code I tried

Dim ws As Worksheet
Set ws = Sheet6


Set findvalue = Application.WorksheetFunction.Index(ws.Range("E:E"), Match(1, (ws.Range("A:A") = Me.List_Sales.Value) * (ws.Range("B:B") = Me.List_Details.Value), 0))
Me.txtReturn = findvalue

Can someone please help me how make it correct?

 

[JoeMo]

Assuming your findvalue variable is not an object, try removing the "Set" keyword.

 

[Rick Rothstein]

Assuming your findvalue variable is not an object, try removing the "Set" keyword.

Also, the use of Match in your code line won't work as it is not a built-in VBA function... you will have to affix a call to the WorksheetFunction object for it like you did for the Index call.

 

[NDMDRB]

Actually I did it the same as Vlookup works but I'm not sure about it,

Can you please help me how to get the result I need even with another way?

 

[NDMDRB]

Hello, please I need your help

 

[NDMDRB]

Also, the use of Match in your code line won't work as it is not a built-in VBA function... you will have to affix a call to the WorksheetFunction object for it like you did for the Index call.

Hello Rick Rothstein,

Can you please help me fixing my problem? I'm new with VBA and still didn't get any solution, please I need your help

 

[Fluff]

How about

Dim ws As Worksheet
Dim FindValue As Variant
Set ws = Sheet6


FindValue = Application.Index(ws.Range("E:E"), Application.Match(1, (ws.Range("A:A") = Val(Me.List_Sales.Value)) * (ws.Range("B:B") = Val(Me.List_Details.Value)), 0))
If Not IsError(FindValue) Then
   Me.txtReturn = FindValue
Else
   Me.txtReturn = "Not found"
End If

 

[NDMDRB]

Thanks for your time Fluff, but I still get an error (Run-time error '13': Type mismatch)

 

[Fluff]

What are you entering into
List_Sales and List_Details
are they text or numerical?

 

[NDMDRB]

What are you entering into
List_Sales and List_Details
are they text or numerical?

Numbers