I have a range that I have named ZeroRepeats. The range is 5 rows and 7 columns. I need to enter a formula into a cell (that is not in the range) to calculate the average of the 5 values in the first column of the ZeroRepeats range. Mostly, my problem is that I don't really know how to reference cells within my named range.
Thanks in advance!
try
=AVERAGE(INDEX(zerorepeats,,1))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Here is how the suggested code looks after VBA Autoformats it:
Range("C10") = Average(Index(ZeroRepeat, , 1))
The code gives me a complie error "Sub or Function not defined", and highlights the word index. Any thoughts?
The range is named currectly (I have already successfully used it here):
Please Login or Register to view this content.
Last edited by arlu1201; 03-03-2013 at 02:36 PM.
Range("c10").FormulaR1C1 = "=average(INDEX(zerorepeats,,1))" works
Oh yes I forgot the formula R1C1. Except now the cell containing the (C10) has a name error. the formula in the cell looks like this
=AVERAGE(INDEX(ZeroRepeat, , 1))
I am assuming because the worksheet does not know what ZeroRepeat is? Then range was named in VBA only. The other option is that I can select the 5 cells I need somehow using this reference:
Please Login or Register to view this content.
Davidrias,
Welcome to the forum.
I have added code tags to your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I was trying to select the range I need like this
This is not workingPlease Login or Register to view this content.
Last edited by davidrias; 03-03-2013 at 02:50 PM.
Try this
or another wayPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by mike7952; 03-03-2013 at 05:23 PM.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Perhaps something like thisPlease Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
If ZeroRepeat is a VBA variable rather than a Named Range, how about this, changing the A1 to suit.
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Yes, that works beautifully. Thank you.
I declared ZeroRepeat as a Range but this code still seems to work.
Yes, that works beautifully. Thank you.
I declared ZeroRepeat as a Range but this code still seems to work.
You declared ZeroRepeat to be a Range variable in VBA.
I got ya. I am quite new to this as you could probably tell. Thank you for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks