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

Report
-
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
-
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

4 replies

Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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
Hi TrowaD, thanks for the reply!

I did those steps you listed above and I was given an error that says "subscript out of range". Would you mind running through and giving a quick explanation of each line of code for? I don't quite under stand the purpose of each command, and I think If I did I could alter it myself to make sure the range is acceptable and then add other commands I need as well.

again thanks for the reply!

Kyle
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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