average for cell using FormulaR1C1

[remeng]

I'm having some problems with a formula for a cell. I would like to do something like this, but I recieve a 1004 error and I know it is linked to the "Range" and "Cell" comnands located within the FormulaR1C1. I'm looking for a workaround that will allow me to place the formula into the cell. I would normally just calulate the average of the range, but the formula must be presented due to customer requirements. Help is greatly apreciated.

HORZPTrowcount = Variable as a number

 Range(Cells(HORZPTrowcount, 6)).FormulaR1C1 = "=average(range(cells(3,6),cells(" & HORZPTrowcount & ",6))"

I have also tried this, but I get a Code 13 error "Type Missmatch"

Range(Cells(HORZPTrowcount, 6)).FormulaR1C1 = "=average" & Range(Cells(3, 6), Cells(HORZPTrowcount, 6)) & ")"

 

[AnotherHiggins]

There are several things wrong with the way you're trying to do this.

I'd suggest you turn on your macro recorder (Tools > Macro > Record New Macro) and add the function you want to appear in the worksheet. Then observe the code that was generated and edit to include HORZPTrowcount.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[chandlm]

This will do as you ask.

MFORMULA = "=average(" & Cells(3, 6).AddressLocal & ":" & Cells(HORZPTrowcount, 6).AddressLocal & ")"
Cells(HORZPTrowcount, 6).Formula = MFORMULA

I should point out however that you are going to create a circular reference with the formula you supplied.


Hope this helps.

Matt

 

[capetownandre]

dont understand why you are wanting to place a formula in a cell programatically.

surley is better to place the result of the formula in the cell, that way the worksheet does not have to recalculate the values everytime you display them.

Or am I missing something
use a variable to build the formula before committing to the cell then by inspecting you can see the formula growing

myformula = " =Average(A3:z" & HORZPTrowcount & ")

 

[chandlm]

Only reason I can see why you would progrmatically add a formula and not the result is, that if you needed to change the values in the workbook it would not reflect in the cell containing the average.


Hope this helps.

Matt

 

[remeng]

Chandlm,

Thank you for the quick response. You are correct that the workbook will be generated through code, but it must also be edited by the user so the cells have to by dynamic. I will give the code a try and get back to you on how it goes.

 

[remeng]

Changed FormulaR1C1 to Formula resolved the problem. Thanks all.