Vlookup in Excel VBA for Data Entry

[manuthenunkal]

HI Excel Expert ,

Request your valuable help in solving the below error .

PS: Am new to Excel VBA coding .

I have got 3 Sheets in the attached workbook “Just combo V2”

Download link : Just combo V2.xlsm

Sheet 1

Sheet 2 =”School” in which Cell A1 : A14 Contains list of schools

Sheet 3 =”Prd” in which B1 to B29= Description , H1 to H29 = Selling Price of each product .

In Sheet 1 ,While the “Add new record” button is pressed , The user form is initiated which prompts us to choose from the drop down list of “school” taken from Sheet “School “ , Choose Product from the drop button which list description of products ( Cell B1 to B29 of Prd ).Qty is to be entered in the text box . Upon clicking :Submit button of the user form ,In Sheet 1 - I would like to see the corresponding selling price of the product in the Cell G .I have tried Vlookup function in different ways and its always showing error .

Sheet is password protected and password is 1234

Many Thanks in Advance

 

[DanteAmor]

Hi and welcome to MrExcel.

No need to search.
We can use the index of the combobox2.
Products are loaded from row 2 of sheet "Prd". If you select the first data of the combo, the index is 0, if you add 2 to 0, we know that this data is in row 2, then we take the price of row 2 column F.
Change all your code to the following:

Private Sub CommandButton1_Click()
'''''''''Valdiation 1 ''''''
  If Me.ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox " Please select the School ", vbCritical
    Exit Sub
  End If
  If Me.ComboBox2.Value = "" Or ComboBox2.ListIndex = -1 Then
    MsgBox " Please select the Product ", vbCritical
    Exit Sub
  End If
  If VBA.IsNumeric(Me.TextBox3.Value) = False Then
    MsgBox "Please enter the correct qty ", vbCritical
    Exit Sub
  End If
  
  Dim sh As Worksheet
  Dim lr As Long, n As Long
  
  Set sh = Sheets("sheet1")
  lr = sh.Range("D" & Rows.Count).End(3).Row + 1
  n = Val(sh.Range("A" & lr - 1).Value) + 1
  
  sh.Range("A" & lr).Value = n
  sh.Range("D" & lr).Value = Me.ComboBox1.Value
  sh.Range("E" & lr).Value = Me.ComboBox2.Value
  sh.Range("F" & lr).Value = Me.TextBox3.Value
  sh.Range("G" & lr).Value = Sheets("Prd").Range("F" & ComboBox2.ListIndex + 2)
  sh.Range("H" & lr).Value = sh.Range("F" & lr).Value * sh.Range("G" & lr).Value
  
  MsgBox "Done", vbInformation
  Call CommandButton2_Click
End Sub

Private Sub CommandButton2_Click()
  Me.TextBox3.Value = ""
  Me.ComboBox1.Value = ""
  Me.ComboBox2.Value = ""
End Sub

Private Sub UserForm_Activate()
  Dim i As Integer
  Me.ComboBox1.Clear
  Me.ComboBox2.Clear
  
  For i = 2 To ShSchool.Range("A" & Rows.Count).End(xlUp).Row
    Me.ComboBox1.AddItem ShSchool.Range("A" & i).Value
  Next i
  
  For i = 2 To ShPrd.Range("B" & Rows.Count).End(xlUp).Row
    Me.ComboBox2.AddItem ShPrd.Range("B" & i).Value
  Next i
End Sub

 

[manuthenunkal]

Dear Dante ,

Many Thanks for your quick feedback !

Now the code works absolutely fantastic . A small change which i made to the code is for populating the sl# as it was giving me errors .

Rest everything is perfect and neat now .

Once again Many Thanks as i was stuck up with this single line for past 3 days

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.