+ Reply to Thread
Results 1 to 8 of 8

Find matching record based on data input in different cell

  1. #1
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Find matching record based on data input in different cell

    I am currently using the following code which when data is entered into G it searches for the first row above that matches, and copies the data from the H column on that row the the H column on the new row:

    Dim lr As Integer
    Dim i As Long, c As Integer
    c = 0
    lr = Cells(Rows.Count, "G").End(xlUp).Row
    For i = lr - 1 To 1 Step -1
    If c = 1 Then
    Exit Sub
    End If
    If Range("G" & i).Value = Range("G" & lr).Value Then
    Range("H" & lr).Value = Range("H" & i).Value
    c = c + 1
    End If
    Next i
    However I would like the adapt it to do the following:
    It should not perform the action until Data has been entered into the E column, and it should act differently depending on what data is entered (the E column has a drop down list). The following is a list of the data that can be entered into G, and what should happen in each case:

    Receive - Nothing should happen.
    Return - Should work the way it currently is (looking for first matching row above, and copying data in H)
    Relocate - Should also work the same way, however the data from H should be entered into the J column instead. (leave H blank)
    Lost - Same as Return (copying data to H)
    Damaged - Same as Return (copying data to H)

    I have attached my workbook for convenience. The Sheet I'm working on is ReturnData.

    Thank you for your time,
    James
    Attached Files Attached Files

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

    Re: Find matching record based on data input in different cell

    Hi James. Just for clarification .... you say
    The following is a list of the data that can be entered into G
    The list that follows corresponds to the drop down in Column E not G. Was this a typo? When you say
    looking for first matching row above
    do you mean looking for the first matching cell in column E above the active cell?

  3. #3
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Find matching record based on data input in different cell

    Correct, the list (Receive, Return, Relocate, Lost, Damaged) are the only entries in a drop down that can be entered into E.
    It should act differently depending on what is entered into this cell.

    Sorry I know I'm probably not explaining it very well. But basically the user usually first enters data into G (which contains serial numbers), after the serial number is entered they will then make a selection from the drop down in E. The next steps should vary depending on what is entered into E, but say they select Return. This should then trigger a search to match the serial in G on this current row to the first record above containing that same serial (within the same G column) then copy the data in H from that record to the current record (so as well as matching in G they now also match in H).
    Last edited by Nitefox; 11-24-2013 at 02:20 PM.

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

    Re: Find matching record based on data input in different cell

    Hi Nitefox. Try this macro.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Find matching record based on data input in different cell

    The code seems to work, however I already have some Worksheet_Change code on that worksheet. When I add the code to the bottom of it, it doesn't work, it only works if I remove the other code. This is the code I already have:
    Please Login or Register  to view this content.
    Another issue is that I have some code which is run by the button Duplicate Data. Since the user will be entering a large amount of serial numbers at a given time (sometimes around 20-40) and usually they will either all be Receiving or Returning, it allows the user to just say select Return in the first record entered then select that cell and hit Duplicate Data to copy it to all the rows below that they just entered serial numbers into. The problem I have is that when data is entered into E that way the code doesn't work (it only works when manually entered one at a time). This is the Duplicate Data code I'm using:

    Please Login or Register  to view this content.
    Also the message box code for "serial number not found" can be removed. If there is no match then nothing should happen.

    Thank you for your time,
    James

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

    Re: Find matching record based on data input in different cell

    Hi James. I had a look at your code and to be honest with you, it is difficult for me to incorporate my code with the code you already have because I don't have an exact understanding of what you are trying to do. I think that you have to start from the top and take it one step at a time, incorporating my code with yours at the appropriate time so it doesn't conflict with what you already have. Unfortunately, I'm not an Excel expert as many of the people on this forum are. Could I suggest that you start a new thread explaining your issue. I'm sure there is an expert out there who will have a solution.

  7. #7
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Find matching record based on data input in different cell

    Thank you for all your help.

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

    Re: Find matching record based on data input in different cell

    Glad to do what I was able.

+ 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] Click on a cell in sheet1 and find matching record on sheet2
    By vbacheck in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2013, 12:14 AM
  2. Replies: 4
    Last Post: 12-23-2012, 04:31 AM
  3. Replies: 4
    Last Post: 06-26-2011, 11:56 PM
  4. How do I find data in a table based on an input cell?
    By wbeck5 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-13-2008, 06:47 PM
  5. macro to find matching record in a different workbook
    By PBM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2008, 08:09 PM

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