Hi,
I am new here and was wondering what Average(R[-29466]C:R[-29185]C
means. I am averaging column but when I look at the columns I am
averaging it is Column E4 to E285. So I am trying to figure out what
the -29466 and -29185 pertains to. It looks like a range to me.
Also, I was wondering why when I open the file, it puts the
Average on Row 29470 and not Row 286. On the output, I don't even see
Row 286..it goes straight from 285 to 29470. The rows in between those
ranges are not hidden. I am so confused.

Thank you in advance for your help.

'Average Calculation
ActiveCell.FormulaR1C1 = "Average"
Range("E29470").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-29466]C:R[-29185]C)"
Range("E29470").Select
Selection.Copy
Range("F29470:H29470").Select
ActiveSheet.Paste
Range("C29470").Select
Application.CutCopyMode = False


+-------------------------------------------------------------------+
|Filename: excel error.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4457 |
+-------------------------------------------------------------------+

--
txexcel
------------------------------------------------------------------------
txexcel's Profile: http://www.excelforum.com/member.php...o&userid=32443
View this thread: http://www.excelforum.com/showthread...hreadid=522258

use average(r4c:r285c) instead, this hardcodes the range similar to
$E$4:$E$285.

txexcel Wrote:
Hi,
I am new here and was wondering what Average(R[-29466]C:R[-29185]C
means. I am averaging column but when I look at the columns I am
averaging it is Column E4 to E285. So I am trying to figure out what
the -29466 and -29185 pertains to. It looks like a range to me.
Also, I was wondering why when I open the file, it puts the
Average on Row 29470 and not Row 286. On the output, I don't even see
Row 286..it goes straight from 285 to 29470. The rows in between those
ranges are not hidden. I am so confused.

Thank you in advance for your help.

'Average Calculation
ActiveCell.FormulaR1C1 = "Average"
Range("E29470").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-29466]C:R[-29185]C)"
Range("E29470").Select
Selection.Copy
Range("F29470:H29470").Select
ActiveSheet.Paste
Range("C29470").Select
Application.CutCopyMode = False


--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=522258

Just to add. the R[-29466]C means to go a cell 29466 rows above the row
containing the formula. It is written in R1C1 notation and the brackets mean
the reference is relative to the cell with the formula.

Based on your code, the formula is being entered in 29470 and the
29470-29466 = 4

If you want the formula at the end of the column, starting in row 4 do

set rng = Range("E4").end(xldown).Offset(1,0)
rng.FormulaR1C1 = "=Average(R4C:R[-1]C)"

this says to go to the next call after the last used cell in column E,
starting in E4, then enter a formula that averages from the absolute\relative
mixed address of R4, same column to the relative\relative address of one row
up and same column.

So I would suspect what you want to perform the whole action (averages in
columns E to H):

Sub AddAverages()
Dim rng as Range
set rng = Range("E4").end(xldown).Offset(1,0)
rng.offset(0,-1).Value = "Average"
rng.Resize(1,4).FormulaR1C1 = "=Average(R4C:R[-1]C)"
End sub

--
Regards,
Tom Ogilvy





"txexcel" wrote:


Hi,
I am new here and was wondering what Average(R[-29466]C:R[-29185]C
means. I am averaging column but when I look at the columns I am
averaging it is Column E4 to E285. So I am trying to figure out what
the -29466 and -29185 pertains to. It looks like a range to me.
Also, I was wondering why when I open the file, it puts the
Average on Row 29470 and not Row 286. On the output, I don't even see
Row 286..it goes straight from 285 to 29470. The rows in between those
ranges are not hidden. I am so confused.

Thank you in advance for your help.

'Average Calculation
ActiveCell.FormulaR1C1 = "Average"
Range("E29470").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-29466]C:R[-29185]C)"
Range("E29470").Select
Selection.Copy
Range("F29470:H29470").Select
ActiveSheet.Paste
Range("C29470").Select
Application.CutCopyMode = False


+-------------------------------------------------------------------+
|Filename: excel error.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4457 |
+-------------------------------------------------------------------+

--
txexcel
------------------------------------------------------------------------
txexcel's Profile: http://www.excelforum.com/member.php...o&userid=32443
View this thread: http://www.excelforum.com/showthread...hreadid=522258