View Full Version : Solved: Too next Together?
fredlo2010
06-24-2012, 09:22 AM
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
No need to loop
Dim varArray
varArray = Array("Header1", "Header2", "Header3", "Header4")
Range("A1").Resize(, UBound(varArray) + 1).Value = varArray
fredlo2010
06-24-2012, 11:36 AM
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
06-24-2012, 11:55 AM
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
06-24-2012, 12:43 PM
Thanks for all the tips and explainations guys
That's why I'd prefer:
Sub snb()
Range("A1").resize(,4)=Array("Header1", "Header2", "Header3", "Header4")
End Sub
Bob Phillips
06-24-2012, 03:24 PM
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
06-24-2012, 03:42 PM
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
06-24-2012, 04:43 PM
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
06-24-2012, 06:13 PM
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")
or
Sub snb()
[A1:D1] = [index("Header "&column(A1:D1),)]
End Sub
Bob Phillips
06-25-2012, 01:52 AM
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
06-25-2012, 06:07 AM
No the heading are not "Heading1", "Heading2"... I do get the picture here thanks a lot guys
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.