Copy Potential data from one sheet to another if condition met Closed

Question

Hello all, thanks for taking the time to read this,

I have a very specific formula request, or at least a request for direction on how to write/learn to write it if this community could help
I have two sheets of data, both potentially tracking the same parts. What I would like to do is have the sheet automatically search the group number in Column A in sheet 1 for a match in column N in sheet 2, and if there descriptions in sheet 1 column K and sheet 2 column L match copy the data from column J sheet 2 to column i sheet 1. 
Is this possible? I have honestly have never really used excel so I don't know the extent of ability of macros and formulas. Please let me know if you need more information.

Thank you for your time,

Kyle

Configuration: Windows 7 / Mozilla 11.0

TrowaD · Answer

Hi Kyle,

Try the following code:
Sub RunMe()
Dim fRange As Range

Sheets("Sheet1").Select

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set fRange = Sheets("Sheet2").Columns("N:N").Find(cell.Value)
    If Not fRange Is Nothing Then
        If cell.Offset(0, 10).Value = fRange.Offset(0, -2).Value Then
            cell.Offset(0, 8).Value = fRange.Offset(0, -4).Value
        End If
    End If
Next cell

End Sub

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

And below is a link where you can find the workbook similating your scenario. 
Group letters D and F are found on sheet2 but only group letter D has a matching description, thus the value 333 will be copied over to sheet1.

Here is the link:
http://ge.tt/2CFqOBe2

Best regards,
Trowa

TrowaD · Answer

Hi Kyle,

Sorry for the late reply, pretty busy lately.

As requested I will do my best to explain the code.

Line 1: Te name of the code.
Line 2: Declaring fRange as range, letting Excel know what we plan to do with the variable fRange (short for find range and can be changed into anything, well almost anything).
Line 4: Selecting sheet1 so that the code can be run from any sheet.
Line 6: Starting the For Each loop within the given range. It starts at row 2 of column A: Range("A2:A" , while this part:  & Range("A" & Rows.Count).End(xlUp).Row) looks for the last used row.
Line 7: This is where we use Find to find the value in column a in column N. When the value is found fRange becomes that range, otherwise fRange stays nothing.
Line 8: If statement. If fRange is range then continue code. If fRange is nothing continue code from line 12.
Line 9: If statement. If the cell 10 columns to the right of the cell from the loop is the same as the cell 2 column to the left of the cell linked to fRange then continue code. If not continue code from line 11.
Line 13: Go to the next cell in the For Each loop.

Let me know if you can figure it out and keep in mind that you can post your workbook the same way I did (carefull with sensitive data), so we can take a look at it.

Best regards,
Trowa

Copy Potential data from one sheet to another if condition met

2 responses

Similar discussions

Subscribe To Our Newsletter!