Display formula in cell (average function)

Question

I have some data:

For each column, I want to calculate the average of the most current year. In the example below, I want to calculate it for 2020, so rows 164, 165 and 166. The result should appear in row 163.

However, I want not just the result, I want the formula so the user can see what is being calculated, but I can't. Maybe relevant: I'm using German Excel. I tried using .FormulaLocal and MITTELWERT(German for AVERAGE) but I think I'm making a different kind of mistake. With both .Formula and .FormulaLocal I'm getting a run time error 13 (type) in the line with the .Formula.

Sub FormulaLeased(ByVal fRow As Long, ByVal lCol As Long, ws As Worksheet)
    Dim i       As Long
    Dim iCol    As Long
    
   'manually setting values for this example:
    fRow = 164
    lCol = 7
    Set ws = ActiveSheet

    With ws
        Select Case Mid(.Cells(fRow, "A").Value, 4, 2) 'Determining the most recent quarter 
        Case "12"
            i = 3
        Case "09"
            i = 2
        Case "06"
            i = 1
        Case "03"
            i = 0
        End Select
        
        For iCol = 2 To lCol
            '---works, but only shows the result, not the forumla:                
            '.Cells(fRow - 1, iCol).Value = Application.WorksheetFunction.Average(.Range(.Cells(fRow, iCol), .Cells(fRow + i, iCol))) 

            '---results in type error:
            .Cells(fRow - 1, iCol).Formula = "= Application.WorksheetFunction.Average(" & .Range(.Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False)) & ")" 

        '---trying to use .FormulaLocal -> Type error
        '.Cells(fRow - 1, iCol).FormulaLocal = "= MITTELWERT(" & .Range(.Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False)) & ")"
        Next iCol
    End With
End Sub

user_3219613 · Answer

Application.WorksheetFunction does not work with .Formula or .FormulaLocal. The .Formula needs the English formula as you would write it in the cell, and .FormulaLocal needs the localized formula (German) as you would write it in the cell.

So this approach was the one to go:

.Cells(fRow - 1, iCol).FormulaLocal = "= MITTELWERT(" & .Range(.Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False)) & ")"

but needs to be changed to

.Cells(fRow - 1, iCol).FormulaLocal = "=MITTELWERT(" & .Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False) & ")"

there is no Range needed just the addresses of the 2 cells.

Note that this code will only work if you run it in a German Excel because MITTELWERT will not be translated to other localizations. If you plan that your code should run on a French Excel too, then you would need to use the English formula and .Formula. This will translate to any localization and therefore work in all Excel versions. I highly recommend to use .Formula and translate them yourself into English (https://excel-translator.de might help):

.Cells(fRow - 1, iCol).Formula = "=AVERAGE(" & .Cells(fRow, iCol).Address(False, False) & ":" & .Cells(fRow + i, iCol).Address(False, False) & ")"

Display formula in cell (average function)

1 Answers