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

 

[Fluff]

Hi & welcome to MrExcel.
Rather than using a textbox for the store number, how about using a combobox? It would make the coding a lot easier
Are you open to that idea?

 

[jdhogan0721]

The issue is that I have about 300 store numbers in my table and thought that may not be as easy to use. Open to ideas though.

 

[Fluff]

Ok, that's fair enough.
With a combo called ComboBox1 you could use this code

Private Sub ComboBox1_Click()
   Dim i  As Long
   
   For i = 1 To 14
      Me.Controls("box" & i).Value = Range("Master").Cells(Me.ComboBox1.ListIndex + 1, i + 1)
   Next i
End Sub

Private Sub UserForm_Initialize()
   Me.ComboBox1.List = Range("Master").Columns(1).Value
End Sub

 

[Fluff]

To continue using the Text boxes, try

   Dim Res As Variant
   With Me
      Res = Application.VLookup(CLng(.box1), Sheet1.Range("master"), 2, 0)
      If IsError(Res) Then
         .Box2 = "Not Found"
      Else
         .Box2 = Res
      End If
   End With

 

[jdhogan0721]

sub/function not defined error. .box1 in the VLookup formula.

Am I correct in placing this as a sub for the go button click?

 

[Fluff]

As long as it's in the userform module then yes.
Are your boxes named Box1 Box 2 etc, or did you leave them as the standard TextBox1, TextBox2 etc?

 

[jdhogan0721]

Named box1 box 2 etc, not creative but I figured this would be easier.

 

[Fluff]

In that case this should work, as long as it's in the Userform Module

Private Sub gobutton_click()
   Dim Res As Variant
   With Me
      Res = Application.VLookup(CLng(.Box1), Sheet1.Range("master"), 2, 0)
      If IsError(Res) Then
         .Box2 = "Not Found"
      Else
         .Box2 = Res
      End If
   End With
End Sub

 

[jdhogan0721]

This worked!! Thank you so much. I didn't realize the sheet name was different in the project editor. You are the best.