VBA Count Cells In A Range And Paste In 5 Strings For Each Count

[Lewzerrrr]

Hey,

So I have a 5 strings of text that I need to duplicate for every cell count in a range.

Example below: there's 5 instances of count (don't include header in range, but this also needs to be dynamic). For every instance I want to repeat String1, String2.. String5 5 times.

Any help is appreciated

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Header1[/TD]
[TD="class: xl63, width: 64"]Header2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Count1[/TD]
[TD="class: xl63"]String1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Count2[/TD]
[TD="class: xl63"]String2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Count3[/TD]
[TD="class: xl63"]String3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Count4[/TD]
[TD="class: xl63"]String4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Count5[/TD]
[TD="class: xl63"]String5[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String1[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String2[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String3[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String4[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String5[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String1[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String2[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String3[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String4[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String5[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String1[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String2[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String3[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String4[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String5[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String1[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String2[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String3[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String4[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]String5[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Oct07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] Rng.Offset(, 1)
    .Copy Destination:=.Resize(Rng.Count * 5)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick