Array question 2

[WaterSprite]

[ myArray1 = Worksheets("sheet5").Range("MyTotal_Data_12") ]

How can I use "ActiveSheet" in place of "Sheet5" in the above line of code. I tried doing: DIM Activesheet as Worksheet,
But I still got an error.

 

[SkipVought]

Hi,

ActiveSheet is an object in the Excel Object Model.

In most cases, ActiveSheet ought to be avoided, as well as ActiveCell and Selection.

Here's something that I might do, not really knowing what you need to accomplish. But WHY an array, when you have a collection?

Dim rgMyRange as Range, wsMySheet as worksheet, r as range

Set wsMySheet = Worksheets("sheet5")

Set rgMyRange = wsMySheet.Range("MyTotal_Data_12")

For each r in rgMyRange
  with r
    debug.print .value, .row, .column, .address
  end with
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougAJ4]

But WHY an array, when you have a collection?

What is the objection to using an array?

It's much faster than working on a range, for one thing.

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[WaterSprite]

Ok, here is a partial of the code. I am trying to learn to
use arrays, this is my first attempt at one that actually worked.

The range("MyTotal_Data")is a named range. I got the original idea from page 390 of Bill Jelens book, VBA and Macro's for Excel. In fact, all of the code below came from his book, I just modified it a little bit. I had figured out how to use the named ranges without having to use a sheet reference. You always tell us not to hard code anything, so I was trying to do this array without having to refer to a specific sheet name every time.

[myArray1 = Worksheets("sheet5").Range("MyTotal_Data_12")]
[For myCount1 = LBound(myArray1) To UBound(myArray1)]
[Worksheets("sheet5").Cells(myCount1 + 8, 19).Value = _
WorksheetFunction.Sum(myArray1(myCount1, 1), myArray1(myCount1, 2), myArray1(myCount1, 3), myArray1(myCount1, 4))]
[Next myCount1]

 

[DougAJ4]

Watersprite - the first thing is if you are using a named range you don't need to qualify it with the Worksheets("sheet5"). Secondly if you have a named range for the output you won't need to hard code the worksheet and location.

But the advantage of using an array is that you can do all the processing inside VBA, then write the results back to the spreadsheet in one operation, and for a large volume of data this is enormously quicker. Try:

Sub ArraySum2()
Dim myArray1 As Variant, myCount1 As Long, myCount2 As Long
Dim NumRows As Long, NumCols As Long, MyTotals() As Double

myArray1 = Range("MyTotal_Data_12").Value2
NumRows = UBound(myArray1)
NumCols = UBound(myArray1, 2)
ReDim MyTotals(1 To NumRows, 1 To 1)

For myCount1 = 1 To NumRows
For myCount2 = 1 To NumCols
MyTotals(myCount1, 1) = MyTotals(myCount1, 1) + myArray1(myCount1, myCount2)
Next myCount2
Next myCount1

With Range("MyTotalsCol")
.ClearContents
.Resize(NumRows, 1).Name = "MyTotalsCol"
End With
Range("MyTotalsCol").Value2 = MyTotals
End Sub

Doug Jenkins

http://newtonexcelbach.wordpress.com/