Excel 2013
In the code below, I have an userform that contains 6 comboboxes and 5 textboxes. When user filled these and click CommandButton1, it will save the data on sheet "East" and then will give me a number on TextBox5 that contains certain datas. Some of these certain datas are from another sheet which name is "West" and related to entered datas on this userform. For example, if I select "E" from ComboBox1, it will give me the value "1" from West sheet and then write this value on TextBox5. I am new to Excel VBA, I tried to write a vlookup formula to draw datas from another sheet but failed and need help. I have attached an example document from my work that you may analyze.
What is the correct way to write this code?
Private Sub CommandButton1_Click()
If ComboBox1.Value <> "" And ComboBox2.Value <> "" And TextBox1.Value <> "" And ComboBox3.Value <> "" And TextBox2.Value <> "" And ComboBox4.Value <> "" And ComboBox5.Value <> "" And ComboBox6.Value <> "" And TextBox3.Value <> "" And TextBox4.Value <> "" Then
Dim lastrow As Long
Worksheets("East").Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Value = ComboBox1.Value
ActiveSheet.Cells(lastrow + 1, 2).Value = ComboBox2.Value
ActiveSheet.Cells(lastrow + 1, 3).Value = TextBox1.Value
ActiveSheet.Cells(lastrow + 1, 4).Value = ComboBox3.Value
ActiveSheet.Cells(lastrow + 1, 5).Value = TextBox2.Value
ActiveSheet.Cells(lastrow + 1, 6).Value = ComboBox4.Value
ActiveSheet.Cells(lastrow + 1, 7).Value = ComboBox5.Value
ActiveSheet.Cells(lastrow + 1, 8).Value = ComboBox6.Value
ActiveSheet.Cells(lastrow + 1, 9).Value = TextBox3.Value
ActiveSheet.Cells(lastrow + 1, 10).Value = TextBox4.Value
TextBox5.Value = WorksheetFunction.VLookup(ComboBox1.Value, West!Range("C7:D11"), 2, 0) & ComboBox2.Value & WorksheetFunction.VLookup(ComboBox3.Value, West!Range("G7:H10"), 2, 0) & WorksheetFunction.VLookup(ComboBox4.Value, West!Range("K7:L16"), 2, 0) & WorksheetFunction.VLookup(ComboBox5.Value, West!Range("F14:G15"), 2, 0) & WorksheetFunction.VLookup(ComboBox5.Value, West!Range("F14:G15"), 2, 0)
MsgBox ("Saved!")
Else
MsgBox "This area must be filled!"
End If
End Sub
Bookmarks