Replace certain cells with certain others, every nth row

[LindaHan]

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...

 

[MickG]

If you count from A2(including A2) what would be the next 3 rows you want the formula /code to change

 

[LindaHan]

A2
a16
a30
a44

 

[MickG]

Try this code:-

[COLOR="Navy"]Sub[/COLOR] MG23Sep35
[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]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = 2 To Lst
    c = c + IIf(n > 2, 1, 0)
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
        [COLOR="Navy"]If[/COLOR] .Row = 2 Or (c) Mod 14 = 0 [COLOR="Navy"]Then[/COLOR]
           .Value = .Offset(, 1)
        [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

 

[MickG]

If you want the formula, try this in "C1" and copy down

=IF(MOD(ROW()+14,14)=2,B1,A1)

 

[LindaHan]

Thanks Mick! I'll try both.