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.


yNEA3Y8HKjoAAAAASUVORK5CYII=



Thanks in advance
Excel 2016
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top