VB combo box with vlookup

[arkusM]

Hello I have the following code:

Private Sub Vendor_Combo_AfterUpdate()
    Pricing_TB = Application.WorksheetFunction.VLookup(Vendor_Combo, Range("Del_ID"), 5, 0)
End Sub

I am trying to populate a TB in a user form with the value selected in the combo box. but it is failing as soon as I click on the Vendor_combo box and I am not sure why, as it is set to do the vlookup after update.
Ideas?

 

[Norie]

How exactly have you populated this combobox?

And where is the data in it and the related data coming from?

 

[arkusM]

How exactly have you populated this combobox?

And where is the data in it and the related data coming from?

fair enough. The combo box is populated in a cascading fashion (with code that I scabbed together from here <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 12pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/biggrin.gif" src="file:///C:\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape>) <o></o>
Combo1 sets the options for combo2 and combo2 for Combo3.<o></o>
Comb3 is the Vendor_Combo in the code and is populated using the code below.<o></o>
The data is stored in a pivot tables, actually, as it is dynamic. There are 5 p-Tables that display the data.

The vlookup comes from a named range...<o></o>
<o></o>
Vendor_Combo is the last combo box that is populated and I was trying to get it to look up a price point that is associated with the value in Vendor_combo. The combo boxes populate as I expect them to. But when I tried to added the "afterChange" on the Vendor_combo to run the Vlookup, well that is where I ran into trouble.
I hope that answers your questions Norie.

Private Sub Del_Combo_AfterUpdate()
'Delievery Point Combo Box
    Vendor_Combo.Clear
    
    Set x = Worksheets("Contract-Control")
    Dim Pipe_Sel As Range, u, v, w As Integer
    Dim varFind As Variant
    
    With x
        u = Range("COMBO_Vend").Offset(2, 0).Row()
        v = Range("COMBO_Vend").Offset(2, 0).Column()
        Set Pipe_Sel = Range(.Cells(u, v), .Cells(u, v + 12)) 'Add a count?
        'MsgBox Pipe_Sel.Address
        'MsgBox u
    End With
    
    With Pipe_Sel
        Set varFind = .Find(What:=Pipe_Combo.Text, LookIn:=xlValues)
            'MsgBox varFind
            If Not varFind Is Nothing Then w = varFind.Column() - 1
                'MsgBox varFind.Address
                'MsgBox w
            Set varFind = Nothing
    End With
    i = Range("Combo_Row")
   With Vendor_Combo
        .Clear
        Do Until IsEmpty(x.Cells(i, w))
        .AddItem (x.Cells(i, w))
        i = i + 1
        Loop
    End With
End Sub

 

[arkusM]

Before you spend too much time let me check something with named range... I'll post back in a few moments... Cheers

 

[arkusM]

What I thought it might be it was not the issue. THis still does not work.
(I forgot the simple vlookup rule, define your range to include all your data (dummy). LOL.)

 

[Norie]

Perhaps I'm missing something but I don't see a lot (any?) vlookupping (that's a real word by the way, honest ) going on in the posted code.

 

[arkusM]

The posted code in the last post was answering your

How exactly have you populated this combobox?

The vlookup is happening (or at least is supposed to be happening) after an option in the vendor_combo has selected, then vlookup returns a value in a Textbox (Pricing_TB), I am trying to make the below happen:

Private Sub Vendor_Combo_AfterUpdate()
    Pricing_TB = Application.WorksheetFunction.VLookup(Vendor_Combo, Range("Del_ID"), 5, 0)
End Sub

sorry for the confusion.

 

[Norie]

My bad

Should spend more time actually reading posts than creating new words.

How exactly is the code failing?

Does it return any value?

Does it error? If so, how?

 

[arkusM]

LOL. No worries.

I guess the error message would help. (Plus, now I am feeling stupid because I probally could have googled the error...)
Runtime error '1004'
Unable to get the Vlookup property of the worksheetFunction class


Sometimes you get so wrapped up in the problem you forget the basic stuff....
Sidebar: do you know why when you hit F1, with cursor over the word "vlookup", in the vba code window that it cannot find the help article?)

 

[Norie]

Have you tried the lookup on the worksheet?

I ask because generally that error means it just isn't working.

And you aren't going to find help on Vlookup in VBA help, it's a worksheet function.

However when I select it in VBA and hit F1 I get taken to a general help page regarding using worksheet functions in code.

It has a link which lists all the available functions.

And in that list there are links to the Excel help topic for all those functions.