Replace certain cells with certain others, every nth row

LindaHan

New Member
Joined
Sep 23, 2014
Messages
7
I need to replace A2 with B2, then again at every 14th row. There are 7622 rows, so I don't want to cut & paste every time...
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG23Sep46
[COLOR="Navy"]Dim[/COLOR] Lst         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = 2 To Lst
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
        [COLOR="Navy"]If[/COLOR] .Row = 2 Or .Row Mod 14 = 0 [COLOR="Navy"]Then[/COLOR]
            Temp = .Offset(, 1).Value
            .Offset(, 1).Value = .Value
            .Value = Temp
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You could try this in column "C" and then copy back!!
Code:
=IF(OR(ROW()=2,MOD(ROW(),14) = 0),B2,A2)
 
Upvote 0
There's no one-way solution to this with a formula as you want to keep values in between the 14 lines.
Closest is probably to go with MickG's and then copy back as he suggests.
 
Upvote 0
MickG's suggestion only works for rows 2 - 15. When I need the formula to replace A16 with B16 it doesn't work, it returns the value in A16 into C16.
 
Upvote 0
If you would like to try the code follow the instruction below :-

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick
 
Upvote 0
It's still not working. Both the code and the formula only work for A2 and B2, but not A16 and B16, etc.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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