Array Help

[Guest]

Below is a sub that I wrote to multiply range "a" and range "b" to get range
"c". Range "a" would be a1:a10, range b would be b1:b10, range "d" would be
"c1:c10". It is not working and am getting an "object required" error. I am
assuming that the error message refers to darray but is darray not an object?
I am writing this code so I could avoid functions in the worksheet where my
rows changes often. I don't want to copy and paste the function but just
have the excel automatically perform the calculation upon an event change.
Also, if you could refer me to a book or website where there is more array
examples or information, it would be appreciated.

Sub test()

Dim myarray As Variant
Dim mycount As Integer

aarray = Worksheets("sheet1").Range("a")
barray = Worksheets("sheet1").Range("b")
darray = Worksheets("sheet1").Range("d")


For mycount = LBound(aarray) To UBound(aarray) '0 to 3
darray(mycount, 1).Value = WorksheetFunction.Product(aarray(mycount,
1), barray(mycount, 1))
Next mycount

End Sub

 

[Alan Beban]

Arrays aren't necessary; simply array enter into Range("C1:C10") =a*b

If you have to do it in VBA, Range("c1:c10").FormulaArray = "=a*b"

Alan Beban

 

[Ken]

Daviv

Alan is pretty much the expert on arrays, so if he says you don't need
arrays, you don't need arrays. If you want a generic code that
populates a third named range with the product of corresponding values
in two similarly sized ranges, you might try:

Sub test()

Dim mycount As Integer
Dim aarray As Range
Dim barray As Range
Dim darray As Range

Set aarray = Worksheets("sheet1").Range("a")
Set barray = Worksheets("sheet1").Range("b")
Set darray = Worksheets("sheet1").Range("d")

For mycount = 1 To aarray.Rows.Count
darray.Cells(mycount, 1) = aarray.Cells(mycount, 1) *
barray.Cells(mycount, 1)
Next mycount

End Sub

Good luck.

Ken
Norfolk, Va