User Form Lookup Error

[ardykav]

HI,

I have the below code and it keeps erroring out

What I am trying to do is in a userform write an asset ID in the txtpart box, once that comes up it then searches Stock tab for that asset ID in Column A and should then return whatever is in column B for that asset, next text box (.txtloc) field. Problem i have is though it keeps erroring and is saying the Stock.Range Bit is the problem, its says object variable or with block variabvle not defined

Any ideas?

Private Sub Txtpart_AfterUpdate()
If WorksheetFunction.CountIf(Stock.Range("A:A"), Me.txtPart.Value) = 0 Then
MsgBox "Not in Stock"
Me.txtPart.Value = ""
Exit Sub
End If
With Me
.txtLoc = Application.WorksheetFunction.VLookup(CLng(Me.txtPart), Stock.Range("Lookup"), 2, 0)
End With
End Sub

 

[AlphaFrog]

Where do you declare and define the worksheet variable Stock ? That appears to be an object variable that is not defined.

You may need something like this at the start of your procedure...

    Dim Stock As Worksheet
    Set Stock = Worksheets("Stock")

 

[ardykav]

Thanks but that is still returning an error. Here is the whole piece of code, the info being put in txt part would be letters and numbers, not sure if this makes a difference

Private Sub Txtpart_AfterUpdate()
Dim Stock As Worksheet
Set Stock = Worksheets("Stock")
If WorksheetFunction.CountIf(Stock.Range("A:A"), Me.txtPart.Value) = 0 Then
MsgBox "Not in Stock"
Me.txtPart.Value = ""
Exit Sub
End If
With Me
.txtLoc = Application.WorksheetFunction.VLookup(CLng(Me.txtPart), Stock.Range("Lookup"), 2, 0)
End With
End Sub

 

[AlphaFrog]

CLng(Me.txtPart)

Why are you converting the text to Long type if txtPart is letters and text?

What's the cell address of Stock.Range("Lookup") ?

 

[ardykav]

Thanks I think I have worked it out now, the issue was I hadnt got the named range lookup defined. What should i be using instead of CLng if its numbers and letters? thanks for your help,