How to generate a repeating text sequence

I'm struggling to create the VBA to achieve the following...

Replace whatever text is in cells in a column with a repeated sequence of text values.

For example:-

Cell...
x1 = "Text1"
x2 = "Text2"
x3 = "Text3"

Then repeat...
x4 = "Text1"
x5 = "Text2"
x6 = "Text3"

Then repeat...
x7 = "Text1"
x8 = "Text2"
x9 = "Text3"

... and repeat until the end of data in cell xn (a blank cell).

Has anyone a clever piece of VBA code to achieve this please? Many thanks. F

Formula in Y1

="Text"&MOD(ROW(A1)-1,3)+1

Then copy down

VBA

Please Login or Register  to view this content.

Many thanks for your rapid response Yasser.

Your routine looks nicely efficient. I should have been a little clearer however with what I am trying to achieve , which is...

Cell...
x1 = "Blue - Dark (287)"
x2 = "Blue - Light (284"
x3 = "Orange (021)"

Then repeat...
x4 = "Blue - Dark (287)"
x5 = "Blue - Light (284"
x6 = "Orange (021)"

Then repeat...
x7 = "Blue - Dark (287)"
x8 = "Blue - Light (284"
x9 = "Orange (021)"

Many thanks if you can help further. F

That works brilliantly Yasser. Very many thanks. You are a star! ;-)) Regards, Frank