VBA VLOOKUP
 

I have a userform in my spreadsheet <frmEntry which includes a combo box
<cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in sheet
<Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS

VBA VLOOKUP
 

I think you have set your range incorectly you are asking the function to
find column 2 in a range that only has one column,

VLookup goes
(Find this, in this range, return value from this coulumn, exact match)

Wild Guess but your range should be a1:b2






"D.S." wrote in message
...
I have a userform in my spreadsheet <frmEntry which includes a combo box
<cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in sheet
<Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS

VBA VLOOKUP
 

DS,

VLOOKUP expects a Range or an Array as the second argument, so you use it
like

Answer = Application.Vlookup(LookupValue, Range("A1:C100"), 3, False)

or in your case (altough the range seems weird, since you only have one
column)

frmentry.lblDesc.Caption = Application.Vlookup(frmentry.cmbMachine.Value,
Range("PartNumbers!C1:C2"), 2, False)

not sure if you wanted to use C1:D2 instead...

--
Regards,

Juan Pablo González
Excel MVP

"D.S." wrote in message
...
I have a userform in my spreadsheet <frmEntry which includes a combo box
<cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in sheet
<Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS

VBA VLOOKUP
 

Thanks,
I tried changing the code to this, but now I'm getting a "script out of
range" error.

frmEntry.lblDesc.Caption =
WorksheetFunction.VLookup(frmEntry.cmbMachine.Valu e,
Sheets("PartNumbers").Range("A2:B77"), 2, False)

D.S.

"D.S." wrote in message
...
I have a userform in my spreadsheet <frmEntry which includes a combo box
<cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in sheet
<Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS

VBA VLOOKUP
 

Try assigning formula arguments to a variable first
Dim PartNo As String
Dim Vrange As Range

PartNo = frmEntry.cmbMachine.Value
Set Vrange = Sheets("PartNumbers").Range("A2:B77")
frmEntry.lblDesc.Caption = _
WorksheetFunction.VLookup(PartNo, _
Vrange, 2, False)

Mike F

"D.S." wrote in message
...
Thanks,
I tried changing the code to this, but now I'm getting a "script out of
range" error.

frmEntry.lblDesc.Caption =
WorksheetFunction.VLookup(frmEntry.cmbMachine.Valu e,
Sheets("PartNumbers").Range("A2:B77"), 2, False)

D.S.

"D.S." wrote in message
...
I have a userform in my spreadsheet <frmEntry which includes a combo box
<cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in sheet
<Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out
here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS

VBA VLOOKUP
 

Also PartNo may need to be further qualified as:
PartNo = UserForm("frmEntry").ComboBox("cmbMachine").Value

Mike F
"Mike Fogleman" wrote in message
...
Try assigning formula arguments to a variable first
Dim PartNo As String
Dim Vrange As Range

PartNo = frmEntry.cmbMachine.Value
Set Vrange = Sheets("PartNumbers").Range("A2:B77")
frmEntry.lblDesc.Caption = _
WorksheetFunction.VLookup(PartNo, _
Vrange, 2, False)

Mike F

"D.S." wrote in message
...
Thanks,
I tried changing the code to this, but now I'm getting a "script out of
range" error.

frmEntry.lblDesc.Caption =
WorksheetFunction.VLookup(frmEntry.cmbMachine.Valu e,
Sheets("PartNumbers").Range("A2:B77"), 2, False)

D.S.

"D.S." wrote in message
...
I have a userform in my spreadsheet <frmEntry which includes a combo box
<cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in
sheet <Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out
here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS

VBA VLOOKUP
 

that didn't seem to help.

DonaldDonald
"Mike Fogleman" wrote in message
...
Also PartNo may need to be further qualified as:
PartNo = UserForm("frmEntry").ComboBox("cmbMachine").Value

Mike F
"Mike Fogleman" wrote in message
...
Try assigning formula arguments to a variable first
Dim PartNo As String
Dim Vrange As Range

PartNo = frmEntry.cmbMachine.Value
Set Vrange = Sheets("PartNumbers").Range("A2:B77")
frmEntry.lblDesc.Caption = _
WorksheetFunction.VLookup(PartNo, _
Vrange, 2, False)

Mike F

"D.S." wrote in message
...
Thanks,
I tried changing the code to this, but now I'm getting a "script out of
range" error.

frmEntry.lblDesc.Caption =
WorksheetFunction.VLookup(frmEntry.cmbMachine.Valu e,
Sheets("PartNumbers").Range("A2:B77"), 2, False)

D.S.

"D.S." wrote in message
...
I have a userform in my spreadsheet <frmEntry which includes a combo
box <cmb.Machine and a label <lbl.Desc.

The combo box is populated with values from column 'A' in another sheet
<Part Numbers during the userform initialization event.

I would like to code the <lbl.Desc caption by using a VLOOKUP of the
selected value in the combo box to look up the appropriate value in
sheet <Part Numbers column 'B'

The following code is giving a COMPILE ERROR, can anyone help me out
here?

frmentry.lblDesc.Caption=worksheetfunction.vlookup (frmentry.cmbMachine.Value,'PartNumbers'!C1:C2,2,f alse)

DS