R1C1 issue

Hi

Can anyone out there help me please!!

I'm trying to enter this formula into a cell via VBA but the date of the 19/05/2015 needs to be a variable but can't get it to work. It needs to refer to today's date when you run the macro.

ActiveCell.Offset(2, 0).Formula = "=AVERAGEIFS (Report1!H:H,Report1!G:G,""<>19/05/2015"",Report1!F:F,"">31/12/2013"",Report1!F:F,""<01/01/2015"")"

I've converted some of the code to R1C1 to refer to a cell in the spreadsheet that has the date see below. I'm not sure how to translate H:H etc into R1C1. Any one help please? And if you know an easier way to use a variable please let me know.

'ActiveCell.Offset(2, 0).Formula = "=AVERAGEIFS(Report1!H:H,Report1!G:G,>R[-2]C[0], Report1!F:F,"">31/12/2013"",Report1!F:F,""<01/01/2015"")"

Thanks in advance.

Mike

Hi

Appreciate the quick response. Simply when you know how. I'll give it a try tomorrow. The May 2015 date is in a different column. May date is in column G and the 2014 date is in column F.

I did try the today() formula but for some reason it didn't work. I'll try it again tomorrow and see what the error message was. Maybe I didn't get the syntax correct.

Thanks all for your help.

Thanks for that. I'll try that as well tomorrow. A space!! How annoying.

Hi

So the Today() worked like a charm so thanks for the help. Naturally now that's done they now want to be able to choose a date. I've created a variable in VBA to store the date that's the easy bit. I've changed the VBA code below. Should that work though as the DateUsed variable shouldn't work in a formula in a cell? It appears to work but wanted to check. Also when I change it to = instead of <> it doesn't work at all. I've tried ""=&DateUsed"" and ""=""&DateUsed but doesn't work.

Can I ask for your help again please?

Thanks

ActiveCell.Offset(21, 0).Formula = "=COUNTIFS(Report1!G:G,""<>&DateUsed"",Report1!F:F,"">31/12/2013"",Report1!F:F,""<01/01/2015"",Report1!H:H,"">=0"",Report1!H:H,""<=30"")" ' (4.1)