Recording a Macro??

[Glenn]

This is my first time recording a Marco.
I would like to know if I have a formula that contains 10 cells
=SUM(U20:U29)/COUNTIF(U20:U29,">0") I would like the Macro to repeat the same
formula in the next set of cells =SUM(U53:U62)/COUNTIF(U53:U62,">0")
Is there a way to set up the macro to replicate the formula 24 cells down?
This way I could use the macro reproduce my formula? I have 550 people with
18 cells to complete going through 6 worksheets and I am doing each one by
hand.

 

[Jim Cone]

I think this is what you want? Select a cell, run the code.
The selected cell and the one 24 rows below will contain the two formulas...
'--
Sub AddThemUp()
Dim rng As Range
Set rng = ActiveCell
rng.Formula = "=SUM(U20:U29)/COUNTIF(U20:U29,"">0"")"
rng.Offset(24, 0).Formula = "=SUM(U53:U62)/COUNTIF(U53:U62,"">0"")"
Set rng = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA



"Glenn" <[email protected]>
wrote in message
This is my first time recording a Marco.
I would like to know if I have a formula that contains 10 cells
=SUM(U20:U29)/COUNTIF(U20:U29,">0") I would like the Macro to repeat the same
formula in the next set of cells =SUM(U53:U62)/COUNTIF(U53:U62,">0")
Is there a way to set up the macro to replicate the formula 24 cells down?
This way I could use the macro reproduce my formula? I have 550 people with
18 cells to complete going through 6 worksheets and I am doing each one by
hand.

 

[Shane Devenshire]

Hi,

You haven't made the question clear.

1. You could show us a sample of your data?
2. Do you only want this for two rows?
3. You want to copy the formula 24 rows down but I notice in you example
that the formula is adjusting by 34 rows. Is that intentional or a mistake?
That means that the formula gets an average for cells that are further and
further away from the formula as you copy it down?
4. The formula you are giving us will not return the average of a set of
data containing numbers less than zero. Is this intentional? Will the range
contain 0's or just blanks or both?

If you were trying to repeat the formula every 24 rows with the cell
references adjusting the same amount, you don't need a macro, it would take
you 5 seconds to do it manually.

 

[Glenn]

Hello Shane,

1) I do not know how to show you a sample of my data.
I would like to show you what I mean. I will do my best to explain what it
is. I have a workbook that contains 6 worksheets that will compile data from
2 week period each worksheet. Each worksheet has a set of input cells. The
cells that I described in the previous post are collecting cell that are
compiling the average from the input worksheet. There is a worksheet called
Averages this consists of a single cell that collects all the information
from the worksheet used for inputs and place in a average cell. This is where
the macro or any idea you might have to do this work easier would come in
handy. Each person has a block of averaging collecting cells that consists of
18 cells the first 6 cells are 6 weeks of Game Knowledge the next 6 cells
equaling 6 weeks are Game Pace and the final 6 cells equaling 6 weeks are
Customer Skills. What I am doing is using my formula to get the info from the
worksheet placing into a cell on the average sheet and then go the next
person for the same week only moving down to the next persons week which
begins 24 cells down. Does this help you understand what I am doing? if not I
would be happy to send you a smaller copy of the workbook so maybe that could
help you understand. I have being working on this for the past 6 months.
First the formulas and now writing the program. Any hlpe would be very
helpful.