Using Numeric Variables for the Cell parameters

You can't take the average of Text values (i.e. 3am and 4am), so I'm assuming that you wanted to average the values in the cells below those places. Then, this call should work as you want:

WorksheetFunction.Average(Range(Cells(2,3), Cells(2,4)))

When in VBA, to use an Excel function, you just need to specify "WorksheetFunction" in front. Hope that helps!

I'm not sure I understand -

For this example, you want it to calculate the average of the cells directly under 3am and 4am (3am and 4am being the user inputs), and you want that average put in the cell directly under "Average" for each of the rows that you have data in?

And you already have a "For" Loop that calculates all of these averages?

Not to question your intentions - but why do you need the formula to show up in the formula bar? I'm pretty sure that you can't do that as you want per se. You could use vba to actually set the formula for say Cell B6 (where the Average would be).

That may look something like:

Cells(2, 6).Formula = "=Average(" & Range(Cells(2, 3), Cells(2, 4)).Address & ")"

You could put that in a Loop and VBA wouldn't actually calculate the averages, it would just put the appropriate formulas down the "Average" column. Actually, I just thought of an even easier way of doing that.

Instead of looping, if it's always the average using the same columns, try these 2 lines of code instead of the full loop.

Cells(2, 6).Formula = "=Average(" & Range(Cells(2, 3), Cells(2, 4)).Address(rowabsolute:=False) & ")"
Range("F2").AutoFill Destination:=Range("F2", Range("E2").End(xlDown).Offset(0, 1))

Hope something in here helps!

Now I'm starting to understand what it is that you want I think.

If you go to "Tools" > "Options" > "General"
and then select "R1C1" reference style

Then, where you want your formulas, you just specify each Row and column numerically - (i.e. "B4" = "R4C2" since "B" is the second column and you wanted the 4th row).

As far as I know, this is the only way to refer to a specific column by number rather than letter in Excel (not VBA).