VBA can not find Values in Cells, formatted as table.

[p82fly]

Hello,
I am pretty new to VBA and have been wrecking my brain and reading just about every Thread there is on this and still can't figure out why I am not getting the code to work.
I am trying to get data from Column "Sale Price", stored in Table "MasterInventory" on worksheet "Master Inventory" to populate a textbox in a UserForm by means of Vlookup.
Upon running the code below I'm getting Value Error 1004, and during Debug when I hover over "MasterInventory" it shows "MasterInventory=Empty"

Also Im trying to figure out how to do it so I can call the "userform" up from any worksheet and add the entries in the table on worksheet (Jan, Feb, Mar, etc.) for the month depicted in the TextBox "Date" on the Userform

If anybody can help I would highly appreciate it.

The code looks like this:

[COLOR=#333333]
[SIZE=1]Private Sub CBx_PROD_AfterUpdate()[/SIZE][/COLOR]
[SIZE=1][COLOR=#333333]'lookup value in Col F [Sale Price] based on Product (Col A [Description] in Table [MasterInventory])[/COLOR][/SIZE]
[SIZE=1][COLOR=#333333]With TB_SP[/COLOR]
[COLOR=#333333]If OB_Y.Value = True Then[/COLOR]
[COLOR=#333333]Me.TB_SP.Value = Application.WorksheetFunction.VLookup(CBx_PROD.Value, MasterInventory, 6, False).Value[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]If CBx_PROD.Value = "" Then[/COLOR]
[COLOR=#333333]Exit Sub[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End Sub[/COLOR][/SIZE]

Thanks in advance.

 

[skywriter]

One possibility is there's a hidden space or something in the name of your worksheet and you don't realize it.

It might also be better to not have the name of the table and the worksheet exactly the same. Maybe name the worksheet wsMasterInventory or the table tblMasterInventory or change them both, or have a variable wsMasterInventory and set it equal to Worksheets("MasterInventory"). Just some suggestions.

 

[Norie]

You can't refer to a table just with it's name, you'll need to use something like Range("MasterInventory").

Also, are the values you are looking up in the table numeric?

If they are then you'll need to convert the text from the combobox to numeric.

 

[p82fly]

Thanks for the reply. How do you convert a text string into numbers??? Never heard of that before?
can you elaborate a little please?

 

[p82fly]

thanks Bruce I'll try the rename thing, see what that does.

 

[Norie]

Try this.

Dim Res As Variant

    Res = Application.VLookup(Val(CBx_PROD.Value), Range("MasterInventory"), 6, False)

    If Not IsError(Res) Then
         Me.TB_SP.Value = Res
     End If

 

[p82fly]

Thanks Norie,
Now I don't get an Error anymore but it still does not find any data in the table. I tried it without the "If Not ISERROR" and when testing it shows that Res="" Error 2042.

Lookupvalue is txt and return value is number but it seems like, no matter what, it won't find any data on the sheet or in the table.

Any more ideas? Thanks for your time and effort.