Excel VBA - loop code problem

[ellis_x]

Hello,

I am a newb at VBA programming, but I am trying to get a loop code t
run. A simple example in below.


Code
-------------------

Sub Macro2()

' This loop runs until there is nothing in the next column

Range("D1").Select

Dim n As Variant

n = 1
Do
ActiveCell.FormulaR1C1 = "=Average(Cells(n, 1), Cells(n, 2))"
ActiveCell.Offset(0, 2).Select
n = n + 1
Loop Until IsEmpty(Cells(n, 1))

End Sub

-------------------


Hopefully someone here will be able to spot what is wrong with wha
I've done here and be kind enough to help.

What is posted here, just puts "=Average(Cells(n, 1), Cells(n, 2))
iin each of the cells - instead of calculating the number .

Using

Code
-------------------

ActiveCell.FormulaR1C1 = "=Average(Cells(n, 1).Value, Cells(n, 2).Value)"

-------------------

comes back with an error.

Cheers

Elli

 

[Binzelli]

Ellis,

the problem is that because everything in the expression

Code
-------------------
"=Average(Cells(n, 1), Cells(n, 2))
-------------------
is between quotes it is put into the cell as is.
If you would want the n values to be calculated into your formula yo
would use a syntax like:

Code
-------------------
"=Average(Cells(" & n & ", 1), Cells(" & n & ", 2))
-------------------
which would result in a string that changes with the n value.

But... that does not solve your problem, because the above string doe
not result in a valid formula

I would recommend using the following syntax, which is not dependent o
the row number:

Code
-------------------
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:RC[-2])
-------------------

This formula calculates the average of the values in two cells
relative to the currently selected cell.
The first cell is 3 columns to the left "RC[-3]" of the curren
position, the second is 2 columns to the left "RC[-2]".

If you would want to calculate the average of two values in columns
and B and put the average in column D, until there are no more value
in column A, you could use the following code:


Code
-------------------

Sub Macro3()

Dim Row As Integer

Range("D1").Select
Row = 1

Do
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:RC[-2])"
ActiveCell.Offset(1, 0).Select
Row = Row + 1
Loop Until IsEmpty(Cells(Row, 1))

End Sub

 

[Samura]

Hi. I fixed the two points.


Sub Macro2()

Dim n As Integer

Range("D1").Select

n = 1

Do

'needed to write the R1C1 formula.
ActiveCell.FormulaR1C1 = "=Average(RC[-3], RC[-2])"

'the ActiceCell shifts under the current cell
ActiveCell.Offset(1, 0).Select

n = n + 1

Loop Until IsEmpty(Cells(n, 1))

End Sub

 

[papou]

Hi Ellis
Sub Macro2()
Dim n As Long
n = 1
Do While Not IsEmpty(Cells(n, 1))
Rng1 = Cells(n, 1).Address
Rng2 = Cells(n, 2).Address
Cells(n, 3).Formula = "=Average(" & Rng1 & "," & Rng2 & ")"
n = n + 1
Loop
End Sub

HTH
Cordially
Pascal