PDA

View Full Version : Match/Index VBA for UserForm with multiple criteria



chan180
09-22-2015, 01:52 PM
Hi there

I'm having some trouble geting this to work.

I've create a userform where I want to be able to input the Company, Province and Description and based on that to reference a data sheet of the different prices for these.
I haven't been able to get Match/Index to work for this :(

Can someone please help?

This is what I have so far.

Private Sub Description_AfterUpdate()

Dim ws As Worksheet
Dim x As Long
Dim fCell As Range
Set ws = Worksheets("Prices")

With ws
'Find the cell which has rec nunmber. Much faster than looping
Set fCell = ".Range(""Row"",Index(Prices,Match((Prices[Company]=txtCompany.Value)*(Prices[Province]=txtProvince.Value)*(Prices[Description]=txtDescription.Value),6),0))"

'If record doesn't match, do nothing
If fCell Is Nothing Then Exit Sub
x = fCell.Row

Me.txtPrice.Value = .Cells(x, 6).Value
End With

End Sub


I've tried a few ways so far. And it doesn't quite seem to work.
If someone can help me would be greatly appreciated!

Thank you

Mike

Peter Mole
01-27-2016, 10:00 PM
Can you provide a short sample of the data table where you are wanting to look up / return these values?