Copy if match - offset

E

emukiss10

Board Regular
Joined
Nov 17, 2017
Messages
201
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.
 
Sort by date Sort by votes
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:
Code: 
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
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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"
 
Upvote 0
You can try this:
Code: 
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
 
Upvote 0
Doesnt work.. I dont know.. Ill try to explain one more time. part of the code is not what I described. (in red)


Code: 
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.
 
Last edited:
Upvote 0
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")
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]DG[/TD]
[TD]DH[/TD]
[TD]DI[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]123[/TD]
[TD]WHITE[/TD]
[TD]data[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]456[/TD]
[TD]CAR[/TD]
[TD]data[/TD]
[TD]PLANE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]789[/TD]
[TD]APPLE[/TD]
[TD]data[/TD]
[TD]BANANNA[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]987[/TD]
[TD]CAT[/TD]
[TD]data[/TD]
[TD]MOUSE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]654[/TD]
[TD]FLY[/TD]
[TD]data[/TD]
[TD]DIG[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]321[/TD]
[TD]HOLE[/TD]
[TD]data[/TD]
[TD]SPACE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]159[/TD]
[TD]TAR[/TD]
[TD]data[/TD]
[TD]DIRT[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]753[/TD]
[TD]BLEACH[/TD]
[TD]data[/TD]
[TD]CLEAN[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]852[/TD]
[TD]WALTZ[/TD]
[TD]data[/TD]
[TD]POLONEZ[/TD]
[/TR]
</tbody>[/TABLE]


SHEETS("FIFTH")
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1111[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2222[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]654[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD].......[/TD]
[TD].......[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3333[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4444[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]159[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD].......[/TD]
[TD].......[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5555[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6666[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
How to copy a range of data if two cells match
Replies
2
Views
268
rockychelsea
R
L
  • Solved
Index and match function spanning multiple columns and rows
Replies
2
Views
336
L8NightKebab
L
S
  • Question Question
Copy certain column N number of times
Replies
0
Views
235
sm789
S
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
152
Ltuk85
L
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
925
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top