Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - loop code problem
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - loop code problem
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 ------------------- Good luck -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - loop code problem
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 -- Samura "ellis_x " wrote in message ... Hello, I am a newb at VBA programming, but I am trying to get a loop code to 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 what 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - loop code problem
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 "ellis_x " a écrit dans le message de ... Hello, I am a newb at VBA programming, but I am trying to get a loop code to 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 what 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 Ellis --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - loop code problem
thanks everyone - your comments/suggestions/fixes have been valuable
: -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Loop some code | Excel Discussion (Misc queries) | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
Excel VBA code required (for.......next loop) | Excel Programming | |||
VBE code in a loop | Excel Programming | |||
Excel VBA - Help with a loop, compare, delete problem | Excel Programming |