Troubleshoot: averaging a dynamic range of cells across a row

Hi all,

I got stuck with a simple problem but I don't know where I gone wrong, I have been haggling with it in the past hour so any help would be greatly appreciated!

Optional background information:
I have a row of dates going across in row 2, and random values going across in row 3 from D3 onwards. I want to return the average of values in row 3 until the third last non-blank cell in row 2 and do a similar thing for maximum row but just for the last 30 cells from the right (so latest 30 days). I have attached screenshot of part of spreadsheet for better illustration.
Capture.PNG

In my macro I can come up with a range for both, as below. Both lines of macro below select the correct range I am looking for.

Please Login or Register  to view this content.

However when I try to apply them into an average/maximum function they returned an error 1004, Application defined or object define error:

Please Login or Register  to view this content.

When I put it as following it returned me the value of Cells (3, 4) (Cell D3) instead of the average:

Please Login or Register  to view this content.

Can anyone tell me what did I do wrong please?

Thanks a lot in advance!

To write into a formula, try:

Please Login or Register  to view this content.

In your attempts, you are mixing VBA commands and Excel formulae incorrectly.

Yes - use FormulaR1C1, and rewrite your range statements to express the necessary range in R1C1 notation.

So, something like:

Please Login or Register  to view this content.

Returns a relative column reference for the first range argument of the average range...

You have to make sure you separate the stuff which only works within a formula, and wrap it in "quotes", and the stuff which only makes sense in VBA, and leave it outside the quotes.

Make sense?

Glad you figured it out. The '&' simply joins up expressions into one string.