+ Reply to Thread
Results 1 to 3 of 3

Command Button with VLookup Formula

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Command Button with VLookup Formula

    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

    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!")

    MsgBox "This area must be filled!"

    End If

    End Sub
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    Breda, NL
    MS-Off Ver
    2016, 2019

    Re: Command Button with VLookup Formula

    Hi, the biggest issue in this matter, is when addressing to other worksheets, you have to address them correctly, like:
    Please Login or Register  to view this content.
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016

    Re: Command Button with VLookup Formula

    @ doloressjk

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-02-2016, 10:27 PM
  2. Insert Command Button that will Activate Command Button on a Different Sheet
    By realvirtuality1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2016, 02:11 PM
  3. VBA code to create command button. Print all sheets then delete command button.
    By Declamatory in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-15-2015, 05:18 PM
  4. Adding a vlookup into VBA command button ?
    By radgrad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-17-2014, 11:55 AM
  5. VLookup fromula referencing a listbox and executed by a command button
    By raymondjdawson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2012, 09:55 PM
  6. Command Button to switch VLOOKUP Values
    By inxs23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2010, 08:15 PM
  7. Replies: 1
    Last Post: 09-17-2007, 09:57 PM

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1