vlookup help

[jdhogan0721]

I am fairly new to vba and thought I could take on a small userform. Joke is on me, a simple vlookup is kicking my butt.

I have a userform that I simply want to use to lookup data from my spreadsheet. I don't need to edit anything, just display data in textboxes.

Static named range is - master

In column "A" I have a store number, when I enter the store number I want the details to populate into textboxes on the userform.

I have a button cleverly named gobutton that I want to click and have it lookup the store number and populate the boxes.

To save myself the hassle of naming a bunch of boxes I simply called each text box "box1" "box2" and so on.

box1 is the textbox I will use to input the store number to be searched.

box2 - box14 are for their corresponding columns. ex: box2 is for column b, box3 for column c and so on.

Here is what I have tried so far (i don't have the file on this computer so I can't copy and paste, bear with me).

Private Sub gobutton_click

With Me

.box2 = application.WorksheetFunction..VLookup(CLng(me.box1), Sheet1.Range("master"), 2, 0)

End With

End Sub

Not sure what I'm missing - getting an object required error

Any help is appreciated!!

 

[jdhogan0721]

Quick question on error management. I still get an error when I enter a record number that isn't in the table. Is there a way to loop through all of column a prior to displaying info. Basically, if you enter an existing number in box1 that is in column a then it displays the records as it does now. If the item number doesn't exist it just gives a message box.

 

[Fluff]

Just make this change

      If IsError(Res) Then
         [COLOR=#ff0000]MsgBox [/COLOR]= "Not Found"
      Else
         .Box2 = Res
      End If