VB combo box with vlookup

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560
Hello I have the following code:
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
How exactly have you populated this combobox?

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

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560
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" o:preferrelative="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><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><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:p></o:p>
Combo1 sets the options for combo2 and combo2 for Combo3.<o:p></o:p>
Comb3 is the Vendor_Combo in the code and is populated using the code below.<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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.

Code:
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

Well-known Member
Joined
Apr 12, 2007
Messages
560
Before you spend too much time let me check something with named range... I'll post back in a few moments... Cheers
 

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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.:eek:
 
Last edited:

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560

ADVERTISEMENT

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:
Code:
Private Sub Vendor_Combo_AfterUpdate()
    Pricing_TB = Application.WorksheetFunction.VLookup(Vendor_Combo, Range("Del_ID"), 5, 0)
End Sub

sorry for the confusion.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
My bad:oops:

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

Well-known Member
Joined
Apr 12, 2007
Messages
560
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top