You can avoid looping through the worksheet cells by loading their
values into VBA in one shot:
' get data from sheet
Dim vArray as Variant
vArray = ActiveSheet.Range("A1:M20").Value
' vArray is a variant containing a 20 row x 13 column array
' matching the size of the range
You can now treat vArray as a VBA array, option base 1:
iMax = UBound(vArray, 1) ' # rows
jMax = UBound(vArray, 2) ' # columns
Looping through the VBA array is much faster than looping through the cells.
You need to define the size of the output range when putting data back
into the range:
' put data into sheet
With ActiveSheet.Cells(1, 1).Resize(20,13)
.Value = vArray
End with
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
count wrote:
Thanks Peter, Tom
The reason I asked is that I'am a purist I suppose - old Cobol coding school
:)
So I like code tight, efficent and tidy.
My example was simplified version of what I needed.
In reality I have this shoe manufacturer, whom I writing application to take
orders, accumulate them to a point and then generate Production Order Sheet
when they feel like it. This sheet, you guessed it, is aggregated sum of
identical orders, telling production people what model to make and what
components to use and whom to send goods.
What is "identical" in shoe industry? Easy -13 basic components must be the
same: soles, tops, heels and 10 more...
So I find myself over and over moving these 13 adjacent / related pieces of
data:
1. From input form to Spreadsheet cells
2. From cells to Viewing form (same as Input but no change allowed)
3. From cells to comparison routines
4. ..... to sorting routines
5. ..... to listboxes on some quick glance forms (sortable, of course, by
columns :)
6........to another sheet where Production sheets details are kept
there's more of course...
So I believe it would be nice to avoid doing these:
For n = 1 to 13 over and over
(in fact I protected my bum in the future, allowing for more elements - yes!
maybe they'll start fitting digital watches or microprocessors in them.. who
knows... and I declared a named range called ShoeElements, where 13 names
are listed and, I use them extensively.
So I really do:
For n = 1 to Range("ShoeElements").Rows.Count - which I consider clever :)
But: I'd still prefer to use a construct - if it was available....
ShoeWholeThing = Range("ThisShoeDetails") 'ShoeWholeThing being User Type
with 13 sub guys listed
.....do some operations on selected ShoeWholeThing.Heel maybe .SoleColor etc
then post them all back with
Range("ThisShoeDetails") = ShoeWholeThing
I'm glad I told you about shoes - off my chest !
:)
Best regards
Paul
Uzytkownik "Peter T" napisal w
wiadomosci ...
Hi Mr or Miss Count
If CarDetailsRange is a single row or single column maybe
one of these possibilities. First put a mixture of data
types in A1:C1 and A5:A8
Sub Test()
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim r1 As Range, r2 As Range
Set r1 = Range("A1:C1")
Set r2 = Range("A5:A8")
v1 = r1.Value
v2 = r2.Value
v3 = WorksheetFunction.Transpose(r2.Value)
For i = 1 To 3
Debug.Print v1(1, i), v2(i, 1), v3(i)
Next
Range("A2:C2") = v1
Range("B5:B8") = v2
Range("A3:C3") = v3
Range("C5:C8") = WorksheetFunction.Transpose(v3)
End Sub
Could you not work directly with the range object?
Regards,
Peter
-----Original Message-----
Thanks Tom,
So it looks that User type is tidier but not shorter way
to disseminate
data.
And looping iterations are still required, using or not
With ...End With
construct.
........If only named ranges were possible I could then
say:
MyCar = CarDetailsRange
.... statements
CarDetailRange=MyCar ' hope cells get populated ok
Ehhh...
Dim MyCar As Car
Dim rng as Range
Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type
Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub
Once the values are populated, you can retrieve their
values using the
same
construct (observing lifetime and scoping rules).
msgbox mycar.CarMilage
--
Regards,
Tom Ogilvy
"count" wrote in message
.. .
Hi,
Can you provide an example (if possible) of usage of
Private Type of
the
following structu
CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean
In particular, I need to get a grasp how using of
Private Type can help
populating UserForm and then sheet cells. Will it be
possible with them
to
eliminate the need for named ranges? Or the opposite
is true?
Hope it's easy to answer :)
Paul
.