Copy text box value to sheet after vlookup and next available row

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
I have a userform that has a combo box (cboEmployee). This combo box is a drop down list of employees the user will pick from. Then there's a text box (Textbox1) the user will enter data in. There are 5 tabs representing each day of the week (Monday...Tuesday...Wednesday an so on). Each day of the week has the employees in column A. What I'm trying to accomplish is to lookup the employee (which was picked by the user in the combobox on the uerform) on Mondays tab and paste the value from the text box 1 in the next available row in Column C. The cells that are yellow is where I will be putting data in from the userform. So this is just the start of this project, but I figure if I get this one figured out I should be able to manipulate the code for the rest of things.





Thanks in advance
Excel 2016
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,194
Office Version
2007
Platform
Windows
I have a couple of doubts

I have a userform that has a combo box (cboEmployee). This combo box is a drop down list of employees the user will pick from. Then there's a text box (Textbox1) the user will enter data in. There are 5 tabs representing each day of the week (Monday...Tuesday...Wednesday an so on).

Each day of the week has the employees in column A.

What I'm trying to accomplish is to lookup the employee (which was picked by the user in the combobox on the uerform) on Mondays tab
What do you need to do the search for?

and paste the value from the text box 1 in the next available row in Column C.
I do not understand the employee's relationship and the next available row in column C

The cells that are yellow is where I will be putting data in from the userform.
Do you want to put more data from userform in the cells?

So this is just the start of this project, but I figure if I get this one figured out I should be able to manipulate the code for the rest of things.

Thanks in advance
Excel 2016
However, I advance the following:

Code:
Private Sub CommandButton1_Click()
  If cboEmployee.Value = "" Then
    MsgBox "Enter employee"
    Exit Sub
  End If
  If TextBox1.Value = "" Then
    MsgBox "Enter textbox"
    Exit Sub
  End If
  Dim sday As String, f As Range, lr As Long, sh As Worksheet
  sday = Format(Date, "dddd")
  Set sh = Sheets(sday)
  Set f = sh.Range("A:A").Find(cboEmployee, , xlValues, xlWhole)
  If Not f Is Nothing Then
    lr = sh.Range("C" & Rows.Count).End(xlUp).Row + 1
    sh.Cells(lr, "C").Value = TextBox1.Value
  Else
    MsgBox "The employee does not exist"
  End If
End Sub
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
I need to search in column A for the employee the user picked in the userform combo box. So if the user picks the employee with a name of Tim Webster then it will need to put the text box 1 value in the next available row under his name. In this example if text box 1 the user enters P1, this value would be placed in cell C120. And yes I will be putting more data from the userform in. TextBox 2 Value will be entered in Column D, Text Box 3 value will be entered in column H and text box 4 will be in column I. If I could attach the file I would But it wont let me.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,194
Office Version
2007
Platform
Windows
Did you try the code I put?
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
I did and it put the text box value at the end of the sheet instead of putting it on the next line of the lookup value.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,194
Office Version
2007
Platform
Windows
Sorry, but your example is unclear.
You could explain your example again.
You can use an image or a file to support your explanation.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
This should better explain what I'm trying to do

I have a userform that the user will pick from the combo box list and then enter in a value in a textbox 1. Once the user clicks the OK command button I would like it to search the combo box value in column A on Sheet1 and place the value from textbox 1 in the next available row below in column C on the same sheet.

Column A has values in A7, A21, A35, A49 and so on. There's a value every 14 rows apart all the way down to cell A436.

Example
Combo box value (User picked) = John Doe
Cell A21 has a value of John Doe in sheet1
Textbox 1 value (User entered) = P12
Cell C22 should now be P12

After the OK command button is clicked the combo box and textbox will get cleared and the user can do it again.

Combo box value (User picked) = John Doe
Cell A21 has a value of John Doe in sheet1
Textbox 1 value (User entered) = P45
Cell C23 should now be P45

Thanks in advance
excel 2016
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,194
Office Version
2007
Platform
Windows
Try this:

Code:
Private Sub CommandButton1_Click()
  If cboEmployee.Value = "" Then
    MsgBox "Enter employee"
    Exit Sub
  End If
  If TextBox1.Value = "" Then
    MsgBox "Enter textbox"
    Exit Sub
  End If
  Dim sday As String, f As Range, lr As Long, sh As Worksheet
  sday = Format(Date, "dddd")
  Set sh = Sheets(sday)
  Set f = sh.Range("A:A").Find(cboEmployee, , xlValues, xlWhole)
  If Not f Is Nothing Then
[COLOR=#0000ff]    lr = f.Row[/COLOR]
[COLOR=#0000ff]    Do While sh.Cells(lr, "C") <> ""[/COLOR]
[COLOR=#0000ff]      lr = lr + 1[/COLOR]
[COLOR=#0000ff]    Loop[/COLOR]
[COLOR=#0000ff]    sh.Cells(lr, "C").Value = TextBox1.Value[/COLOR]
  Else
    MsgBox "The employee does not exist"
  End If
End Sub
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
That's absolutely perfect. Thank you so much. But I do have another one since you asked if the userform would be putting other data in. How would I go about having textbox 2 data put in column D, textbox 3 value in column H and textbox 4 value in column I.

Thanks again. So far it's what I'm looking to do
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,194
Office Version
2007
Platform
Windows
That's absolutely perfect. Thank you so much. But I do have another one since you asked if the userform would be putting other data in. How would I go about having textbox 2 data put in column D, textbox 3 value in column H and textbox 4 value in column I.

Thanks again. So far it's what I'm looking to do

Try this:

Code:
Private Sub CommandButton1_Click()
  If cboEmployee.Value = "" Then
    MsgBox "Enter employee"
    Exit Sub
  End If
  If TextBox1.Value = "" Then
    MsgBox "Enter textbox"
    Exit Sub
  End If
  Dim sday As String, f As Range, lr As Long, sh As Worksheet
  sday = Format(Date, "dddd")
  Set sh = Sheets(sday)
  Set f = sh.Range("A:A").Find(cboEmployee, , xlValues, xlWhole)
  If Not f Is Nothing Then
    lr = f.Row
    Do While sh.Cells(lr, "C") <> ""
      lr = lr + 1
    Loop
    sh.Cells(lr, "C").Value = TextBox1.Value
[COLOR=#0000ff]    sh.Cells(lr, "D").Value = TextBox2.Value
    sh.Cells(lr, "H").Value = TextBox3.Value
    sh.Cells(lr, "I").Value = TextBox4.Value[/COLOR]
  Else
    MsgBox "The employee does not exist"
  End If
End Sub
 

Forum statistics

Threads
1,089,450
Messages
5,408,306
Members
403,196
Latest member
annph

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top