Solved: Too next Together?

[fredlo2010]

Hi guys,

I was wondering if there is a way to put two or more Next together for a For.

I have this code that will populate headers on the top row of my workbook.There are quite a few headers so I put them into an array.

so here is my code

varArray = Array("Header1", "Header2", "Header3", "Header4")

With Range("A1")
For n = 0 To 3
For i = 0 To 4

.Offset(0, 0 + i).Value = varArray(n)
Next i
Next

End With

It would work if somehow I could make the Next i and Next run together.

Thanks

[VoG]

No need to loop


Dim varArray
varArray = Array("Header1", "Header2", "Header3", "Header4")
Range("A1").Resize(, UBound(varArray) + 1).Value = varArray

[fredlo2010]

Wow VoG thanks a lot. As always you come up with something new. I have only one more question. Is the dimension of the Array Implied with "Ubound(varArray)" ?

Thanks a lot for the help

[Paul_Hossler]

Sometimes the Option Base setting will be set to 1, so varArray will go from 1 to 4, instead of the expected 0 to 3

Try test() with Option Base 1 and the results are a little different

test1() works with either Option Base



Option Explicit
Option Base 1


Sub test()
Dim varArray
varArray = Array("Header1", "Header2", "Header3", "Header4")
Range("A1").Resize(, UBound(varArray) + 1).Value = varArray
End Sub


Sub test1()
Dim varArray
varArray = Array("Header1", "Header2", "Header3", "Header4")
Range("A1").Resize(, UBound(varArray) - LBound(varArray) + 1).Value = varArray
End Sub


Paul

[fredlo2010]

Thanks for all the tips and explainations guys

[snb]

That's why I'd prefer:


Sub snb()
Range("A1").resize(,4)=Array("Header1", "Header2", "Header3", "Header4")
End Sub

[Bob Phillips]

That's why I'd prefer:


Sub snb()
Range("A1").resize(,4)=Array("Header1", "Header2", "Header3", "Header4")
End Sub



Why the heck would you resize with a constant, instead of using

Range("A1:D1")=Array("Header1", "Header2", "Header3", "Header4")

[fredlo2010]

Why the heck would you resize with a constant, instead of using

Range("A1:D1")=Array("Header1", "Header2", "Header3", "Header4")

My guess, he is just trying to stick to the original post which contains resize ?

[Paul_Hossler]

Why the heck would you resize with a constant, instead of using

Range("A1:D1")=Array("Header1", "Header2", "Header3", "Header4")

If the number of heading cells was varying (or if I had a hard time counting) I think it'd be easier to just let Excel figure it out using LBound and UBound

That way I'd only need to change the Array enteries.


Range("A1").Resize(, UBound(varArray) - LBound(varArray) + 1).Value = varArray


Paul

[fredlo2010]

If the number of heading cells was varying (or if I had a hard time counting) I think it'd be easier to just let Excel figure it out using LBound and UBound

That way I'd only need to change the Array enteries.


Range("A1").Resize(, UBound(varArray) - LBound(varArray) + 1).Value = varArray


Paul

Thanks guys as always, I get more than I ask for in this forum.
BTW regarding the counting thats why I always set in a four rows layout.

varArray = Array("Header1", "Header2", "Header3", "Header4", _
"Header5", "Header6", "Header7", "Header8", _
"Header9", "Header10", "Header11", "Header12")

[snb]

or

Sub snb()
[A1:D1] = [index("Header "&column(A1:D1),)]
End Sub

[Bob Phillips]

or

Sub snb()
[A1:D1] = [index("Header "&column(A1:D1),)]
End Sub

Oh come on! You don't really believe that his actual headings are Header1, Header2, ... do you?

[fredlo2010]

No the heading are not "Heading1", "Heading2"... I do get the picture here thanks a lot guys