hard code the number of rows used with R1C1 macro recording

In cell AU2 I have the following formula I want to use in a macro. I want to be able to hard code the number of rows being using (relative value)

Please Login or Register  to view this content.

With the macro recorder I get the following formula:

Please Login or Register  to view this content.

I want to hard code the "R[21]" portion of formula. I tried changing code to following but it is not working:

Please Login or Register  to view this content.

I was able to write the code without having to use the R1C1 formula. I put the actual formula in the macro but am not able to insert the AgentCount within the formula.

Current formula:

Please Login or Register  to view this content.

I am trying to update to:

Please Login or Register  to view this content.

Thanks in advance for any comments.

Sorry I did not clarify on my last post. I didn't use the macro recorder with the code supplied. I used the formuala desired with slight modifications to make it work in VBA. I want to incorporate the AgentCount, which is in column H (Note: the agent count changes every week). I have attached a sample of spreadsheet with desired results (column AX). Thanks for any comments.

Please Login or Register  to view this content.