Visual Basic script – Applying a formula across a column

This VB script computes a formula accross a column and writes the formula on a range of cells in another column of an Excel file. It can be very useful to compute average / min / max of collection of numbers. Example is demonstrated:

RAW   Average
Index Value   Index Value (AVG)
1 21.95   1 22.73
1 21.21   2 21.82
1 25.02   3 20.91
2 23.66      
2 21.19      
2 20.63      
3 23.31      
3 19.59      
3 19.83      

To apply the script, first select the first cell where the formula should be written.

Sub ComputeAndWriteFormula(formulaName As String, sheetName As String, column As String, startRow As Integer, endRow As Integer, step As Integer)
' This function adds formula starting from starting from the current cell
' The formula will be build in the following way.
' =Formula(Sheet!Column<number>:Column<number+step>

    Dim sheet As String
    Dim formula As String
   
    If Len(sheetName) = 0 Then
        sheet = ""
    Else
        sheet = sheetName + "!"
    End If
   
    For ind = startRow To endRow Step step
        Dim startCell As String
        Dim endCell As String
        Dim endCellRow As Integer
        endCellRow = ind + step - 1
        startCell = column + Trim(Str(ind))
        endCell = column + Trim(Str(endCellRow))
        formula = "=" + formulaName + "(" + sheet + startCell + ":" + endCell + ")"
        ActiveCell.formula = formula
        ActiveCell.Offset(1, 0).Select
    Next
End Sub

Sub TestComputeAndWriteFormula()
    Call ComputeAndWriteFormula("AVERAGE", "raw", "E", 2, 313, 3)

End Sub

Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *