Loop adding text to range of cells across columns

[288enzo]

I sort of started out what I'm trying to do, am I close

Dim i As Long
Dim rng As Range
Dim var As Variant

var = Array("Parts", "Sales", "Service", "Technical", "Total")
Set rng = Range("C2")

For i = 0 To 5

rng.Offset(0, i + 1) = var & Chr(10) & "Certified"
rng.Offset(0, i + 2) = var & Chr(10) & "Active"
rng.Offset(0, i + 3) = var & Chr(10) & "Cert%"

Next i

The final result should look like this

Book4
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Parts Certified	Parts Active	Parts Cert %	Sales Certified	Sales Active	Sales Cert %	Service Certified	Service Active	Service Cert %	Technical Certified	Technical Active	Technical Cert %	Total Certified	Total Active	Total Cert %
Sheet1

 

[Fluff]

Try it like

For i = 0 To UBound(var)

rng.Offset(0, i * 3) = var(i) & Chr(10) & "Certified"
rng.Offset(0, i * 3 + 1) = var(i) & Chr(10) & "Active"
rng.Offset(0, i * 3 + 2) = var(i) & Chr(10) & "Cert%"

Next i

 

[GWteB]

Perhaps you could give this a try ...

Dim i As Long
Dim rng As Range
Dim var As Variant

var = Array("Parts", "Sales", "Service", "Technical", "Total")
Set rng = Range("C2")

For i = 0 To 12 Step 3

    rng.Offset(0, i + 1) = var(i / 3) & Chr(10) & "Certified"
    rng.Offset(0, i + 2) = var(i / 3) & Chr(10) & "Active"
    rng.Offset(0, i + 3) = var(i / 3) & Chr(10) & "Cert%"

Next i

 

[288enzo]

Try it like

For i = 0 To UBound(var)

rng.Offset(0, i * 3) = var(i) & Chr(10) & "Certified"
rng.Offset(0, i * 3 + 1) = var(i) & Chr(10) & "Active"
rng.Offset(0, i * 3 + 2) = var(i) & Chr(10) & "Cert%"

Next i

Thank you, these certainly is condensed. I'm unfamiliar with Ubound and will need to do some reading.

 

[288enzo]

Perhaps you could give this a try ...

Dim i As Long
Dim rng As Range
Dim var As Variant

var = Array("Parts", "Sales", "Service", "Technical", "Total")
Set rng = Range("C2")

For i = 0 To 12 Step 3

    rng.Offset(0, i + 1) = var(i / 3) & Chr(10) & "Certified"
    rng.Offset(0, i + 2) = var(i / 3) & Chr(10) & "Active"
    rng.Offset(0, i + 3) = var(i / 3) & Chr(10) & "Cert%"

Next i

I was so close, adding Step is new for me. I am happy though that I was able to put most of it together.

Thanks for the help.

 

[GWteB]

I am happy though that I was able to put most of it together.

It is always most satisfying when you get things done yourself. Well done!

Glad we could help and thanks for letting us know.

 

[Joe4]

Thank you, these certainly is condensed. I'm unfamiliar with Ubound and will need to do some reading.

You use UBOUND when dealing with arrays. It is literally "Upper Bound".
When you use that, you don't need to hard-code the size of the array. It makes it dynamic, saying "up to the last entry".
It is nice because you can then add/remove values to your array, and not have to adjust the loops in your code.

See: UBound function (Visual Basic for Applications)

 

[288enzo]

You use UBOUND when dealing with arrays. It is literally "Upper Bound".
When you use that, you don't need to hard-code the size of the array. It makes it dynamic, saying "up to the last entry".
It is nice because you can then add/remove values to your array, and not have to adjust the loops in your code.

See: UBound function (Visual Basic for Applications)

That makes sense. Thanks, I'll have to play around with that. I'm just getting used to how to writing in one way.