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