|
lookup for a combo box in VBA
Ok, thanks to Patrick I have my combo box working. Of coarse there is a but,
the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
lookup for a combo box in VBA
What you don't say is where the data is so I will make assumptions.
You are getting a number from the Combobox, and so all you use is ws.Cells(iRow, 4).Value = Application,VLOOKUP(Me.cboVend.Value, _ Range("H1:M100"),2,False) ws.Cells(iRow, 7).Value = Application,VLOOKUP(Me.cboVend.Value, _ Range("H1:M100"),3,False) assuming that the data is in H1:M100, and that vendor and ranch are in I and J respectively, columns 2 and 3 of the data table. -- HTH RP (remove nothere from the email address if mailing direct) "Jennifer" wrote in message ... Ok, thanks to Patrick I have my combo box working. Of coarse there is a but, the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
lookup for a combo box in VBA
Hi,
Generall format for VLOOKUP is: where MyVal is Lookup Value e..g Me.cboVend.Value myRng is Lookup table e.g VendorList? n is the column in the lookup range with only two columns n=2 as first column is the loolup value False means the table does not have to be in sorted order Dim res as Variant res = Application.VLookup(myVal, myRng, n, False) If IsError(res) Then MsgBox "No match" Else MsgBox res End If ws.Cells(iRow, 4).Value =res So for Vlookup: res=Application.Vlookup(me.cbovend,VendorLisdt,2,f alse) If IsError(res) Then MsgBox "No match" Else End If "Jennifer" wrote: Ok, thanks to Patrick I have my combo box working. Of coarse there is a but, the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
lookup for a combo box in VBA
Sorry, finger trouble!
So for your Vlookup: res=Application.Vlookup(Me.cboVend.value,VendorLis t,2,false) If IsError(res) Then MsgBox "No match" Else ws.Cells(iRow, 4).Value =res End If If you have two columns in your combobox i.e number and vendor name then you could use: ws.Cells(iRow, 4).Value =Me.cboVend.List(Me.cboVend.Value - 1, 1) HTH "Toppers" wrote: Hi, Generall format for VLOOKUP is: where MyVal is Lookup Value e..g Me.cboVend.Value myRng is Lookup table e.g VendorList? n is the column in the lookup range with only two columns n=2 as first column is the loolup value False means the table does not have to be in sorted order Dim res as Variant res = Application.VLookup(myVal, myRng, n, False) If IsError(res) Then MsgBox "No match" Else MsgBox res End If ws.Cells(iRow, 4).Value =res So for Vlookup: res=Application.Vlookup(me.cbovend,VendorLisdt,2,f alse) If IsError(res) Then MsgBox "No match" Else End If "Jennifer" wrote: Ok, thanks to Patrick I have my combo box working. Of coarse there is a but, the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
lookup for a combo box in VBA
More correctly ...
ws.Cells(iRow, 4).Value =cboVend.List(cboVend.ListIndex, 1) "Toppers" wrote: Sorry, finger trouble! So for your Vlookup: res=Application.Vlookup(Me.cboVend.value,VendorLis t,2,false) If IsError(res) Then MsgBox "No match" Else ws.Cells(iRow, 4).Value =res End If If you have two columns in your combobox i.e number and vendor name then you could use: ws.Cells(iRow, 4).Value =Me.cboVend.List(Me.cboVend.Value - 1, 1) HTH "Toppers" wrote: Hi, Generall format for VLOOKUP is: where MyVal is Lookup Value e..g Me.cboVend.Value myRng is Lookup table e.g VendorList? n is the column in the lookup range with only two columns n=2 as first column is the loolup value False means the table does not have to be in sorted order Dim res as Variant res = Application.VLookup(myVal, myRng, n, False) If IsError(res) Then MsgBox "No match" Else MsgBox res End If ws.Cells(iRow, 4).Value =res So for Vlookup: res=Application.Vlookup(me.cbovend,VendorLisdt,2,f alse) If IsError(res) Then MsgBox "No match" Else End If "Jennifer" wrote: Ok, thanks to Patrick I have my combo box working. Of coarse there is a but, the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
lookup for a combo box in VBA
Assuming your combobox is on a userform, then it sounds like you have set
the boundcolumn property to zero. This returns the ListIndex property rather than the value selected. Change it to 1 and it should return what is displayed in the textbox. No need to use vlookup if this is the situation. -- Regards, Tom Ogilvy "Jennifer" wrote in message ... Ok, thanks to Patrick I have my combo box working. Of coarse there is a but, the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
lookup for a combo box in VBA
Thanks everyone. Tom was right about my bound columns. First time using and I
won't make that same mistake I promise. Thank you everyone else for the lookup syntax, I will try, it just to learn. Regards, Jennifer "Tom Ogilvy" wrote: Assuming your combobox is on a userform, then it sounds like you have set the boundcolumn property to zero. This returns the ListIndex property rather than the value selected. Change it to 1 and it should return what is displayed in the textbox. No need to use vlookup if this is the situation. -- Regards, Tom Ogilvy "Jennifer" wrote in message ... Ok, thanks to Patrick I have my combo box working. Of coarse there is a but, the combo box returns a number in the database. Do I have to write a function in the macro to lookup the correct text? I have used vlookup in excel but never in a macro. Help please! Right now in column 4 and 5 there is number instead of the vendors name and ranch name. This is what it looks like thus far: Private Sub cmdAdd_Click() ' copy the data to the database ws.Cells(iRow, 1).Value = "=R[-1]C+1" ws.Cells(iRow, 2).Value = Me.txtInvoice.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.cboVend.Value ws.Cells(iRow, 5).Value = Me.cboRan.Value ws.Cells(iRow, 7).Value = Me.txtPallet.Value ws.Cells(iRow, 8).Value = Me.txtQty.Value ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value ws.Cells(iRow, 12).Value = "Purchase" Me.txtInvoice.SetFocus End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
| All times are GMT +1. The time now is 02:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com