Loop through Variant filling Range

[288enzo]

I'm trying to learn more about looping and using a variant.

How close am I to getting the result

Copy of 2021_CertReadinessReport_ASE_AudiSouthern_10162021.xls
	A
1	Area 80
2	Area 82
3	Area 82a
4	Area 82b
5	Area 83
6	Area 83a
Sheet1

Sub yetanothertest()
Dim rng As Range, m As Long, Var As Variant
Var = Array("Area 80", "Area 82", "Area 82a", "Area 82b", "Area 83", "Area 83a")
Set rng = Range("A1")
For m = 1 To 6
Range("A" & m).Value = Var
Next m
End Sub

Thank you

 

[Marc L]

For m = 0 To 5
Range("A" & m + 1).Value = Var(m)

VBA help must read : Array, LBound & UBound …​

​

Your variable rng is useless !​

​

A better way than a loop : [A1:A6] = Application.Transpose(Var) …​

 

[Marc L]

Or​

For m = 1 To 6
Range("A" & m).Value = Var(m - 1)

 

[288enzo]

For m = 0 To 5
Range("A" & m + 1).Value = Var(m)

VBA help must read : Array, LBound & UBound …​

I hit F1 for help, couldn't understand it. Thanks for the pointers.

Sub yetanothertest()
Dim rng As Range, m As Long, Var As Variant
Var = Array("Area 80", "Area 82", "Area 82a", "Area 82b", "Area 83", "Area 83a")
'Set rng = Range("A1")
For m = 1 To 5
Range("A" & m).Value = Var(m)
Next m
End Sub

 

[Marc L]

​

See post #3 !​

​

The direct allocation : [A1:A6] = [{"Area 80";"Area 82";"Area 82a";"Area 82b","Area 83","Area 83a"}] …​

 

[288enzo]

Or​

For m = 1 To 6
Range("A" & m).Value = Var(m - 1)

if m = 1 to 6, then we start with range A1, and going off the Array why do you use -1? Wouldn't m be "Area 80"? I don't understand using a negative, what that accomplishes. I'll have to play around with the numbers and see what the output is. Thank you

 

[Marc L]

Correction for the direct allocation :​

[A1:A6] = [{"Area 80";"Area 82";"Area 82a";"Area 82b";"Area 83";"Area 83a"}]

As it's explain in the VBA help of Array statement but you can also understand why​

just taking a glance during the execution in the VBE Locals window to see how is your Var array …​

 

[288enzo]

​

See post #3 !​

​

The direct allocation : [A1:A6] = [{"Area 80";"Area 82";"Area 82a";"Area 82b","Area 83","Area 83a"}] …​

This so simple, thank you.
I did have to change the ; to a , for it to work. Maybe it's my version of Excel?

 

[Marc L]

No my bad 'cause I had no time to edit before the site maintenance was launched …​

​

See my post #7.​

 

[288enzo]

Correction for the direct allocation :​

[A1:A6] = [{"Area 80";"Area 82";"Area 82a";"Area 82b";"Area 83";"Area 83a"}]

Ignore me