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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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,false)
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

Sorry, finger trouble!

So for your Vlookup:

res=Application.Vlookup(Me.cboVend.value,VendorList,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,false)
> 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

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,VendorList,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,false)
> > 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>