Basic VBA question
 

I Know this is a very simple question, but as I am just
learning VBA and your help is greatly appreciated.


I have two arrays:

priceArr = .Range("A1:A8").Value
quantityArr = .Range("B1:B3").Value

I would like to make a very simple calculation [price *
quantity = revenue ] for each row and output the result in
colum C (i.e. C1:C8). How do I go about that?

Basic VBA question
 

I assume that you mean B1:B8 for the quantities.

I would be tempted to let Excel do the calculation:

Range("C1:C8").FormulaR1C1 = "=RC[-2]*RC[-1]"

However, you are probably interested in more complex issues and you could do the following in VBA:

Sub test()
Dim priceArr As Variant
Dim quantityArr As Variant
Dim revenueArr As Variant
Dim iRows As Integer
Dim i As Integer

priceArr = Range("A1:A8").Value
quantityArr = Range("B1:B8").Value

iRows = UBound(priceArr)
ReDim revenueArr(1 To iRows, 1 To 1)

For i = 1 To iRows
revenueArr(i, 1) = priceArr(i, 1) * quantityArr(i, 1)
Next i

Range("C1").Resize(iRows, 1).Value = revenueArr

End Sub

Bear in mind that ranges are two dimensional even if only one row or column is involved.

We could have hard coded the upper limit of 8 rows but this way is more flexible. We could also calculate the lower limit.

--

John Green - Excel MVP
Sydney
Australia


"Henrik" wrote in message ...
I Know this is a very simple question, but as I am just
learning VBA and your help is greatly appreciated.


I have two arrays:

priceArr = .Range("A1:A8").Value
quantityArr = .Range("B1:B3").Value

I would like to make a very simple calculation [price *
quantity = revenue ] for each row and output the result in
colum C (i.e. C1:C8). How do I go about that?

Basic VBA question
 

priceArr = .Range("A1:A8").Value
quantityArr = .Range("B1:B3").Value

You have 8 prices and 3 quantities - how to match them up.

Assume both are 8 cells

Dim revenueArr(1 to 8, 1 to 1) as double

With Worksheets("Sheet1")
priceArr = .Range("A1:A8").Value
quantityArr = .Range("B1:B8").Value

for i = 1 to 8
revenueArr(i,1) = priceArr(i,1) * quantityArr(i,1)
Next
.Range("C1:C8").Value = revenueArr
End With

--
Regards,
Tom Ogilvy


Henrik wrote in message
...
I Know this is a very simple question, but as I am just
learning VBA and your help is greatly appreciated.


I have two arrays:

priceArr = .Range("A1:A8").Value
quantityArr = .Range("B1:B3").Value

I would like to make a very simple calculation [price *
quantity = revenue ] for each row and output the result in
colum C (i.e. C1:C8). How do I go about that?