Using Index function in userform

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
128
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))


ABCDEFGHI
1Inv. #NumBarCodeNameCostInv. #NumCost
21011111Test110102330
31012222Test220
41021111Test110
51022222Test220
61023333Test330

<tbody>
</tbody>

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

Here is the code I tried

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

MrExcel MVP
Joined
May 26, 2009
Messages
16,644
Assuming your findvalue variable is not an object, try removing the "Set" keyword.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,404
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Jun 20, 2016
Messages
128
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

Board Regular
Joined
Jun 20, 2016
Messages
128
Hello, please I need your help
 

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
128
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
Office Version
365
Platform
Windows
How about
Code:
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

Board Regular
Joined
Jun 20, 2016
Messages
128
Thanks for your time Fluff, but I still get an error (Run-time error '13': Type mismatch)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
Office Version
365
Platform
Windows
What are you entering into
List_Sales and List_Details
are they text or numerical?
 

Forum statistics

Threads
1,077,797
Messages
5,336,384
Members
399,079
Latest member
DeoW

Some videos you may like

This Week's Hot Topics

Top