Copy if match - offset

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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
We are dealing with 3 sheets here.
So if the number 687 is found on row 9 of sheet(5) does 687 also have to be on row(9) of sheet (3) and then copied to row 9 of sheet(5)
 
Upvote 0

emukiss10

Board Regular
Joined
Nov 17, 2017
Messages
201
2 sheets.

If value of A15 Sheets(5) is found in F544 Sheets(3) than copy contains of DG544 and DI544 to Sheets(5) M15 and N15.
 
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
I'm still confused.
I said:
We are dealing with 3 sheets here.

You said in post 5:
2 sheets.
 
Upvote 0

emukiss10

Board Regular
Joined
Nov 17, 2017
Messages
201
Sir :)

Sheet number 3 and Sheet number 5
- only those two (Sheets(5) and Sheets(3)).
Mayby Im not getting it :) English is not my native language so I might mess something up from time to time :)
 
Last edited:
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
Try this:
Checks every 3rd cell in column "A" sheet(3) against same cell in column "F"
Starts in row(3) then row(6) then (9) etc.

Code:
Sub My_Sub()
'Modified 2-28-18 1:45 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(3).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(3).Cells(i, "DG").Copy Sheets(5).Cells(i, "M")
            Sheets(3).Cells(i, "DI").Copy Sheets(5).Cells(i, "N")
        End If
        Next
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0

emukiss10

Board Regular
Joined
Nov 17, 2017
Messages
201
mayby it could work but I dont know how to remodel it. Could you make it work for my case?

"If value of A15 Sheets(5) is found in F544 Sheets(3) than copy contains of DG544 and DI544 to Sheets(5) M15 and N15."

Check every 3rd cell in column "A" sheet(5) against column "F" Sheet(3)

Starts in row(3) then row(6) then (9) etc.

 
Upvote 0

Forum statistics

Threads
1,186,730
Messages
5,959,417
Members
438,422
Latest member
pisuke

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
Top