PDA

View Full Version : Take the size of the Array



Djblois
04-12-2010, 08:45 AM
I use this code to fill column headings using an array. However, I use almost the same exact code in multiple places. I would rather the code adjust automatically based on the size of the array is that possible:

Here is some of the code:

Range("A1", Cells(1, FinalColumn(wsWorking) + 2)).Value = Array("Whse#", "Whse", "Lot#", "Item#", "Product", "Dept#", "Received", "Shipped", "Adjusted", _
"In Whse", "Reserved", "Allocated", "Held", "Available", "Value")


see how I take the width of columns then I add values to it if it is not long enough? Well I want it to pick up the size of the array automatically - so I do not need to do this and I can use multiple arrarys in the same code.

p45cal
04-12-2010, 09:07 AM
perhaps:x = Array("Whse#", "Whse", "Lot#", "Item#", "Product", "Dept#", "Received", "Shipped", "Adjusted", "In Whse", "Reserved", "Allocated", "Held", "Available", "Value")
Range("A1").Resize(, UBound(x) - LBound(x) + 1) = x
but note that the bit:
UBound(x) - LBound(x) + 1
is complicated since I don't know whether you've set
Option Base
to 1 or 0

Djblois
04-12-2010, 11:36 AM
I have never set the Option Base so it is the default which I think is 0. Am I correct? So I think I would need the +1 then correct?

p45cal
04-12-2010, 12:00 PM
The extra complexity of the formula is to handle either case, so what the option base is doesn't matter.

If I'd just used UBound, then I'd need to know what the Option Base is.