Copy if match - offset

[emukiss10]

Hello!

VBA needed.

In sheets(5) column "A" every 3rd row (A3, A6, A9 and so on) I have some numbers.
I'd like to search for those numbers in column "F" of sheets(3). (F2:F)
If match I would like to copy contains of cells from column "DG" and "DI" sheets(3) to columns "M" and "N" sheets(5) to the rows that was matched.

Best Regards
W.

 

[My Aswer Is This]

My mistake again.
Not trying to make excuses for myself but.
It's always easier when you refer to your sheets by name and not number.
Like sheet named "Master" and sheet named "Update"


Try this:

Sub My_Sub()
'Modified 3-1-18 2:50 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(5).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Cells(Rows.Count, "F").End(xlUp).Row
    For i = 3 To Lastrow Step 3
        
        For Each c In Range("F2:F" & Lastrowa)
        If c.Value = Cells(i, 1).Value Then
            Sheets(5).Cells(i, "DG").Copy Sheets(3).Cells(i, "M")
            Sheets(5).Cells(i, "DI").Copy Sheets(3).Cells(i, "N")
        End If
        Next
Next
Application.ScreenUpdating = True
End Sub

 

[emukiss10]

I think we are missing something..

I am very sorry for all the comotion. But we are getting there I will keep in mind to use names instead of numbers (in this case lets leave it as it is)

Check every 3rd cell from Sheets("fifth") column "A" against column "F" from Sheets("third")
(two sheets are being compared)
and if value from "fifth" is found in "third" than copy cells DG and DI from "third" to N and M of Fifth
Ofc the same row. If Sheets("fifth").Range("A30") is found in Sheets("third").Range("F822") than copy DG822 and DI822 from "third" to M30 and N30 of "fifth"

 

[My Aswer Is This]

I'm just going to let someone else here at Mr. Excel help you.

 

[My Aswer Is This]

You can try this:

Sub My_Sub()
'Modified 3-1-18 5:35 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(5).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(3).Cells(Rows.Count, "F").End(xlUp).Row
    For i = 3 To Lastrow Step 3
        
        For Each c In Sheets(3).Range("F2:F" & Lastrowa)
        If c.Value = Sheets(5).Cells(i, 1).Value Then
            Sheets(5).Cells(i, "DG").Copy Sheets(3).Cells(i, "M")
            Sheets(5).Cells(i, "DI").Copy Sheets(3).Cells(i, "N")
        End If
        Next
Next
Application.ScreenUpdating = True
End Sub

 

[emukiss10]

Doesnt work.. I dont know.. Ill try to explain one more time. part of the code is not what I described. (in red)

Sub My_Sub()
'Modified 3-1-18 5:35 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(5).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(3).Cells(Rows.Count, "F").End(xlUp).Row
    For i = 3 To Lastrow Step 3
        
        For Each c In Sheets(3).Range("F2:F" & Lastrowa)
        If c.Value = Sheets(5).Cells(i, 1).Value Then
           [B] Sheets(5).Cells(i, "DG").Copy Sheets(3).Cells(i, "M") - [COLOR=#ff0000]COPY Sheets(3).Cells(i, "DG").Copy Sheets(5).Cells(i, "M")[/COLOR][/B][B][COLOR=#000000]   
             Sheets(5).Cells(i, "DI").Copy Sheets(3).Cells(i, "N")   - [/COLOR][COLOR=#ff0000]COPYSheets(3).Cells(i, "DI").Copy Sheets(5).Cells(i, "N")[/COLOR][/B]
        End If
        Next
Next
Application.ScreenUpdating = True
End Sub

I tryied to make it work but I failed.

 

[emukiss10]

Bump

 

[emukiss10]

In this example

Sheets("FIFTH") A3 (654) was found in Sheets("THIRD") F5 and I need to copy from Sheets("THIRD") DG5 and DI5 to M3 and N3 Sheets("FIFTH")
the same with A6 (159) and so on till the last row.


SHEETS("THIRD")

	A	B	C	D	E	F	DG	DH	DI
1	data	data	data	data	data	123	WHITE	data	GREEN
2	data	data	data	data	data	456	CAR	data	PLANE
3	data	data	data	data	data	789	APPLE	data	BANANNA
4	data	data	data	data	data	987	CAT	data	MOUSE
5	data	data	data	data	data	654	FLY	data	DIG
6	data	data	data	data	data	321	HOLE	data	SPACE
7	data	data	data	data	data	159	TAR	data	DIRT
8	data	data	data	data	data	753	BLEACH	data	CLEAN
9	data	data	data	data	data	852	WALTZ	data	POLONEZ

<tbody>
</tbody>

SHEETS("FIFTH")

	A	B	C	M	N	O
1	1111	data	data	data	data	data
2	2222	data	data	data	data	data
3	654	data	data	.......	.......	data
4	3333	data	data	data	data	data
5	4444	data	data	data	data	data
6	159	data	data	.......	.......	data
7	5555	data	data	data	data	data
8	6666	data	data	data	data	data

<tbody>
</tbody>

 

[emukiss10]

Bump

 

[emukiss10]

Bump

 

[emukiss10]

Bump