code for using offset function for a combo box

[jarrallian]

hi,

i have a data entry user form with 7 fields including two combo boxes, one for a field named "Medicine".

i want to do the following:

1. link the medicine combo box to a range that contains medicine in 1st column and retail price in the next column to the right.

2. when i select the medicine from data entry form combo box and click add button, it puts the medicine in one cell on my data entry sheet and its retail price in the very next column to the right.

im trying to do this with the following code but i have not succeeded.

is there any way out?

THE CODE IS HERE:

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lDate As Long
Dim ws As Worksheet
Set ws = Worksheets("Purchases")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

lSupplier = Me.cboSupplier.ListIndex

'check for a Date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a Date"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtDate.Value
.Cells(lRow, 2).Value = Me.txtRV.Value
.Cells(lRow, 3).Value = Me.txtINV.Value
.Cells(lRow, 4).Value = Me.cboSupplier.Value
.Cells(lRow, 5).Value = Me.cboMedicine.Value
.Cells(lRow, 6).Value = Me.cboMedicine.List(lMedicine, 1)
.Cells(lRow, 7).Value = Me.txtQuantity.Value
.Cells(lRow, 8).Value = Me.txtNetAmount.Value

End With

'clear the data
Me.txtDate.Value = ""
Me.txtRV.Value = ""
Me.txtINV.Value = ""
Me.cboSupplier.Value = ""
Me.cboMedicine.Value = ""
Me.txtQuantity.Value = ""
Me.txtNetAmount.Value = ""


End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim cSupplier As Range
Dim cMedicine As Range
Dim ws As Worksheet
Set ws = Worksheets("Database")

For Each cSupplier In ws.Range("Supplier")
With Me.cboSupplier
.AddItem cSupplier.Value

End With
Next cSupplier

For Each cMedicine In ws.Range("Medicine")
With Me.cboMedicine
.AddItem cMedicine.Value
.List(.ListCount - 1, 1) = cMedicine.Offset(0, 1).Value

End With
Next cMedicine

Me.txtQuantity.Value = ""
Me.txtNetAmount.Value = ""
Me.txtDate.SetFocus

End Sub

 

[AlphaFrog]

Change this...
lSupplier = Me.cboSupplier.ListIndex

To this...
lMedicine = Me.cboMedicine.ListIndex

You define the variable lSupplier but you don't use it. You use the variable lMedicine but didn't define it.

 

[jarrallian]

Thankssss a lot man. i m such a fool the change worked, thanks thanks thanks .......

 

[AlphaFrog]

I'm glad it worked. Anyone who writes macros regularly has done that at least once.

Consider using Option Explicit to help prevent problems like that.