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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,753
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
7,753
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
7,753
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
7,753
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
7,753
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,077,828
Messages
5,336,621
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top