+ Reply to Thread
Results 1 to 12 of 12

VBA code to lookup cell in one sheet and compare to second then paste value

  1. #1
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    VBA code to lookup cell in one sheet and compare to second then paste value

    Can someone help me out with this VBA. I would like to be able to run a macro that looks at cells C123:H123 on sheet 1 and compare it to column A in sheet 2. If it finds a match (there will always be a match) I want to go back to sheet 1 and get the value in the corresponding column in row 128 and paste that value on the corresponding row in column D in sheet 2. I've attached the file. So when I run this macro the cell C128 value will be entered in under the name from cell C123 in the sheet2 (in this case it's called Employees. Then when ran again it needs to write the same cell value just in the next cell available.

    Mill Bonus 2.xlsm

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,819

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Please Login or Register  to view this content.
    This assumes you won't have more than about 250 employees or so, and that you won't have employees names past column H...but it's easily adjustable.
    Want to show appreciation for the help you received from a member? Give them reps by clicking the bottom left of their post!

  4. #4
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    ok first off that's perfect. You made it a lot easier than what I thought it would be. Been searching for this answer for a week now. Guess I was looking in the wrong spot. So I can't say thank you enough. Now as with anyone in excel you always see something else that would make it easier. So here's what I'm wondering if is possible. Right now it copies and paste the info exactly how I was wanting. But my only issue is that if I run this macro 3 times with the same employees on the "Moulder 1"tab it puts their bonus where it needs to be on the "Employees" tab. But if I put another employee in and run it, it starts their bonus off on the first column which is Sunday. Even though that day might have been a Thursday or something. Again what you have supplied is perfect but do you think there's a way to distinguish the days and to be able to copy the cell data to that day?

    Again thank you very much!

  5. #5
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    I apologize. I just used Mumps1 and didn't see yours. But thank you both.

  6. #6
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    You could just put a drowndown on the Moulder 1 tab and have the macro look at that and assign the paste column off of that instead of finding it.

  7. #7
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Quote Originally Posted by Zimmerman View Post
    I apologize. I just used Mumps1 and didn't see yours. But thank you both.
    No worries, he beat me to it. Honestly I like seeing how other peoples' brains work as well. Helps me get better as I'm still new to this as well.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,819

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    In order to copy to the right column according to day in the "Employees" sheet, we would need the date or day in the "Moulder 1" sheet to act as a link between the two sheets. Does the date you have in cell C1 in the "Moulder 1" sheet correspond to the day you want to reference for each employee in C123:H123?

  9. #9
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Yes it does.

  10. #10
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Quote Originally Posted by Mumps1 View Post
    Try:
    Please Login or Register  to view this content.
    Modified Mumps1's code (since that's what you're using) to account for the weekday properly.
    Last edited by Obsessed; 11-03-2015 at 04:50 PM.

  11. #11
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Wow. This works perfect. Thank bot h of you guys very much!

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,819

    Re: VBA code to lookup cell in one sheet and compare to second then paste value

    Glad to help.

+ 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. [SOLVED] VBA code, cut and paste entire row into new sheet per specific condition in cell
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-07-2015, 08:53 AM
  2. Vba code to compare the 2 sheets and paste the values in 3rd sheet
    By Abinaya in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-02-2014, 01:47 AM
  3. Replies: 4
    Last Post: 05-16-2014, 06:30 AM
  4. VBA Code to copy cell range and paste to certain sheet
    By goguneon in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-03-2013, 08:35 AM
  5. VBA code to copy cell value and paste in different sheet
    By mubashar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 01:26 AM
  6. [SOLVED] How to paste from other sheet to active cell VBA code
    By herukuncahyono in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2013, 02:49 AM
  7. Compare one cells from sheet 2 and paste the value in front of the cell
    By jeevan123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-15-2012, 05:26 AM

Bookmarks

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