Range reference using a variable

  • Please note that I am a beginner user of Excel, so please excuse me if i use the wrong terminology.


    Im writing a macro which will ask the user to enter the desired raw number, then "in the already selected cell" it will calculate the average of a range that starts from the 4th row of the conlomn of the active cell to the entered row number by the user.


    Example:
    active cell is B1, the macro will first ask the user for the desired row number (ex. 250), then the macro will calculate in B1 the average of B4:B250


    This is my failed attempt


    Code
    Sub Macro4()
    Dim Value05 As String
    Value05 = InputBox("when will it reach 0.5?")
    ActiveCell.FormulaR1C1 = "=AVERAGE(B4:B(Value05))"  
    End Sub


    Thanks in advance

  • Try

    Code
    Sub GetAverage()
        Dim sRow As String, lCol As Long
        
        With ActiveCell
            lCol = .Column
            sRow = InputBox("Enter the final Row # for which the average is needed.", "Range To Average.")
            If sRow <> "" Then .Value = Application.Average(.Parent.Cells(4, lCol).Resize(CLng(sRow) - 3))
        End With
        
    End Sub


    It is dynamic in that it will work for any column and average the values from Row 4 to the Row entered in the Input Box for the column of the Active Cell.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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