Application Defined or Object Defined Error, Command Button and Vlookup Function

Hi,

I have a user form, that stays active on the sheet as long as it is open. The form is multipage, and each page has a series of command buttons on it. I'm trying to assign a function to the buttons that uses the tag property in a VLookup function, but i'm doing something wrong. I currently know very little about programming in Excel.

This is what i have:

Please Login or Register  to view this content.

Ideally i'll write this function once and then be able to call the same function for every button, since there will be many. The property tag is associated with a range on another sheet. The VLookup should return the name in the corresponding range, and place it on the "Order" sheet at the specified range location. Basically letting a user select many buttons on the form in succession, to build an order.

Clearly the function isn't completely right since i get a run time error 1004. "Application Defined or Object Defined Error", and it should be placed somewhere else besides the Private Sub for the command button. But that's about as much as i know up to this point.

thanks in advance.

Sorry, yes it should return the value one column to the right once it locates the value in column A that matches the property tag value. Which for reference is just a numerical number like 1016.

Insert the value into a range on another sheet (the active sheet) at K4, while shifting all of the previous values in that range down.

Thanks shg,

I had an error in the range, but once that was adjusted, your function worked.

I've added some more, and now seem to be getting the syntax wrong when inserting a variable into a formula:

Private Sub CommandButton1_Click()
Dim itemNumber As String
itemNumber = CommandButton1.Tag

Dim menuItem As String
Dim itemPrice As String

menuItem = WorksheetFunction.VLookup(itemNumber, Sheets("Items").Range("A:C"), 2)
itemPrice = WorksheetFunction.VLookup(itemNumber, Sheets("Items").Range("A:C"), 3)

With Range("K5")
.Insert Shift:=xlDown
.Value = menuItem
End With

With Range("L5")
.Insert Shift:=xlDown
.Value = itemPrice
End With
End Sub

the itemNumber variable in the VLookup is causing a runtime error 1004. What's the proper way to insert a variable into a formula?