VLOOKUP, Edit Results, Paste Changes

vlookup, edit results, paste changes back to table If I have a table on sheet 2 that consists of a1-employee name, b1-employee id, c1-employee department, d1-employee phone number. I was wondering if there is an easy way to utilize vlookup to do the following:

On sheet 1 the table layout is identical to sheet 2.
1. I want to type in the employees id no and have all the remaining fields fill in. (This part is easy when using vlookup)
2. Then I would like to make any necessary changes, like modifying the employees phone number. Then with a click of a button, the information would be posted back to the table on sheet 2 and replace the existing data with the new info. ( :cool: Challenge)

I am willing to incorporate VBA if necessary. Any recommendations is greatly appreciated. Thanks in advance.

Hi Brorick,

Are you going to want everything on the table of sheet 1 to be transferred to the table of sheet 2 when you hit the button? Or do you want to select an employee ID, hit the change button, then update only that employee?

Matt

I want to update only that employee as I will enter only one employee id at a time.

If that employee ID will be in B2 then the following macro will do what you need:

Code:

---

Sub UpdateEmployee()
 Dim Found As Range, Look As Range
 Set Look = Sheets(1).Range("B2")
 'Set Look = Cells(ActiveCell.Row, 2) 'Uncomment this if you want to select the row to update
 Set Found = Sheets(2).Columns(2).Find(What:=Look.Text, After:=[B1], LookIn:=xlValues, _
  LookAt:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False)
 Found.Offset(0, -1) = Look.Offset(0, -1)
 Found.Offset(0, 1) = Look.Offset(0, 1)
 Found.Offset(0, 2) = Look.Offset(0, 2)
End Sub

---

However, if you want to select a cell someone in the row of the employee to be updated, then uncomment the second Set Look line, and comment out the first.
Do you need any help creating a button or implimenting this? If so, do you want a button on the toolbar or a grey command button on the sheet?

Matt,

Thank you for your help. I will create a button and give it a quick try.

Matt,

You are awesome. I tried three other forums and you are the only one who had a solution to my problem. You exceeded my expectations. The code works like a charm. It is nice and clean. You have made my day. I wish you the best.

Not a problem at all, glad I could help! Let us know if you want any modifications or anything, if you can think it up we can probably code it!

Hello. The code I was given for my original question, still works very well. I was wondering, is there a way to add to the code the following option:

If the employee id number is not found, then add it and the other information to the list. I have a successful update code that works for me, but I am having difficulty combining it with your code. I prefer to keep your code in place and build upon it. Any suggestions on how I can take another approach. After the process of updating the record or adding a new one is complete, maybe a message, update complete, or record added can appear. Again, as always, thanks in advance for anyone?s help.

Code:

---

    Set anchor_cell = Range("data").End(xlDown).Offset(1, 0)
anchor_cell.Value = Range("employeeid").Value
anchor_cell.Offset(0, 1).Value = Range("employeedept").Value

---

I was going to write something like this into the code originally but didn't want to do it without you asking for it :) I was actually able to do this by shortening the above code. First off, here's the new code:

Code:

---

Sub UpdateEmployee()
 Dim Found As Range, Look As Range
 Set Look = Sheets(1).Range("B2")
 Set Found = Sheets(2).Columns(2).Find(What:=Look.Text, After:=[B1], LookIn:=xlValues, _
  LookAt:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False)
 If Found Is Nothing Then Set Found = Sheets(2).Range("B1").End(xlDown).Offset(1, 0)
 Range(Found.Offset(0, -1), Found.Offset(0, 2)) = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value
End Sub

---

I added the line:

Code:

---

If Found Is Nothing Then Set Found = Sheets(2).Range("B1").End(xlDown).Offset(1, 0)

---

That's saying "If the .Find didn't find anything, then set Found to be the next blank line in column B."

I also changed the lines that update the database to take all 4 cells on the first sheet, and put their values in the second sheet.

So if the employee ID # isn't there it will add it. If it is already there, then this won't really do anything since it's just setting it to the same thing it already was.

This way, the reference Found will either be the existing record, or the next blank entry. And all 4 fields get updated, even if they contain formulas.

Let me know if you need any more updates!

Matt. Thank you for the response. I will give it a try.

Quote:

---

Also, other than data validation in Excel, do you know of a good code to prevent duplication? Therefore, if someone tries to add the same employee id number more than once, they will get an error message that tells them this is a duplicate employee id number, please enter a different number. I have many different codes I presently experimenting with for this to work successfully. This question is the least of my worries. Again I really do appreciate any help you can give.

---

I got that from your email (I don't have access to my email while at work, so I had to wait until I got home). One way to do this would be to use a worksheet_change event. Go to the VB editor, press control-r for the project window, double click on the Sheet2 icon (or whatever you have for your second sheet) within the workbook you want this to occur. In the code pane, paste the following:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim chgdCell As Range
 For Each chgdCell In Target.Cells
    If chgdCell.Column = 2 Then
        If Application.WorksheetFunction.CountIf(Intersect(Columns(2), _
        Target.Worksheet.UsedRange), chgdCell) > 1 Then
            MsgBox "This is a duplicate id number, please enter a different number"
            chgdCell.Select
        End If
    End If
 Next chgdCell
End Sub

---

See if that suits your needs, and as always let us know any more ways we can help!
Welcome to vbaexpress :)

I was wondering if it is possible to make a minor modification to Matt's code, so that when it finds the matching record, it will copy it to another location (Sheet 3, cell A1), and if nothing matches, a message would appear, "No matching record found.".

Code:

---

Sub UpdateEmployee()
    Dim Found As Range, Look As Range
    Set Look = Sheets(1).Range("B2")
    Set Found = Sheets(2).Columns(2).Find(What:=Look.Text, After:=[B1], LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Found Is Nothing Then Set Found = Sheets(2).Range("B1").End(xlDown).Offset(1, 0)
    Range(Found.Offset(0, -1), Found.Offset(0, 2)) = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value
End Sub

---

I am still using the code that Matt originally provided and it is working very well. I like how well the code works, I thought it work for me in another scenario. Any assistance is always appreciated. Thank you in advance for your help.

Hello again,
Glad to hear it's working out for you, those modifications are easy enough to make. I just have a couple questions

I can have it also copy the code to another location, but I just want to verify you want only the employee id copied to sheet3 (as opposed to all 4 cells)? And do you want it to always be sheet3, cell A1, or only for the first time and then A2, A3, etc afterwards?

Also, if no match is found, you still want the record added along with the popup message, correct?

Let me know and I'll make those two changes for you right away!
Matt

If possible, I would like to copy all 4 cells to cell a1, b1, c1 and d1 on Sheet 3 each time. Of course, this would replace the previously copied cells on Sheet 3 everytime the code is run and that would be perfect. Having the message pop up, if there is no correct match would be ideal. Is it possible to comment out the option to permit the copied cells to follow the path of a1, a2, a3 on sheet 3 as you described, in case that turns out to be the best course of action? Thank you kindly for your time and assistance.

Hi brorick,

Here is the updated code for you. You didn't say if you wanted the unmatched entry to be added to sheet2, so if you do, simply switch lines 10 and 11 below (numbered on the right). Also, the comments for line 12 and lines 13-14 let you decide which method you want for duplicates (Always first row vs. next available row). Let me know if you have any more additions!

Code:

---

Sub UpdateEmployee()
Dim Found As Range, Look As Range, DupCell As Range, Dup As Boolean '** 1**
Set Look = Sheets(1).Range("B2") '** 2**
Set Found = Sheets(2).Columns(2).Find(What:=Look.Text, After:=[B1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False) '** 3**
Dup = True '** 4**
If Found Is Nothing Then '** 5**
    Set Found = Sheets(2).Range("B1").End(xlDown).Offset(1, 0) '** 6**
    Dup = False '** 7**
    Msgbox "No matching record found" '** 8**
End If '** 9**
Range(Found.Offset(0, -1), Found.Offset(0, 2)) = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value '**10**
If Dup = True Then '**11**
    'Uncomment 12 to always use A1:D1 on sheet 3 for duplicate values (and comment 13-14)
    Sheets(3).Range("A1:D1") = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value '**12**
    'Uncomment 13 and 14 to always use the next available row on sheet 3 for duplicates (comment 12)
    'If Sheets(3).Range("A1") = "" Then Set DupCell = Sheets(3).Range("A1")
 Else
    Set DupCell = Sheets(3).Range("A65536").End(xlUp).Offset(1, 0) '**13**
    'Range(DupCell, DupCell.Offset(0, 3)) = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value '**14**
End If '**15**
End Sub

---

Matt

Hello Matt. You are the "Man of the Year!". Again you have managed to exceed my expectations. I was expecting to take your previous code and new code and mesh it all together. Instead, you simplified my effort and managed to provided flawless lines of code that are, for lack of a better word, ?perfect?.

I want to also extend my gratitude to the person who created this forum. Without it, I would still be wondering aimlessly through lines of coding seeing a glimmer of light at the end of the tunnel to only discover it is a train.

Matt, the simplicity and strength of your code has made learning VBA coding that much more enjoyable. Thanks again.

Very glad to hear it, and thank you very much for the kind words! I am also very grateful for this forum, as it is truly a place for people to help others for nothing more than the satisfaction of helping people!

Of course don't hesitate to ask if there's anything else you need