Using Numeric Variables for the Cell parameters

  • Hi all,

    I have the following table:

    1am 2am 3am 4am 5am
    1 2 3 4 5

    The inputs of this program is the hours, for example, from 3am to 4am.
    Then, the average will calcuate (3+4)/2 ...

    Since I dont know what the user will input, I cannot use A1,B1 .. or index ...
    How can I specify an array containing the data?
    I was thinking since Range(Cells(1,3),Cells(1,4)) refers to A3:A4, then
    Average(" Range(Cells(1,3),Cells(1,4))") should work but it doesnt ...

    It should be a variation of the above ... but I dont know what that is.

    Any help is appreciated !! Thanks!!


  • 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!

  • Thank you Jmhans.

    I was able to get it using VBA codes. However, because my table is actually very large, if I do it in VBA I probably need a loop so to calcuate the average many times. Like this ...

    1am 2am 3am 4am 5am Average
    1 2 3 4 5
    43 34 435 345 454

    I'm wondering how come I cant put "=average(Range(Cells(2,2),Cells(2,3)))"
    in the function window on top of the spreadsheet, while it works in the VBA codes ......

    Hope you can help me again, Thanks!!!


  • 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).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!