Home » Blogs » Nick's blog

XLA routines: EE_AddCalculatedColumn

Nick's picture
Submitted by Nick on 25 January, 2015 - 18:13
EE_AddCalculatedColumn is a hugely useful sub routine that adds a calculated column to your data set, and names it. - this saves writing the VBA to add a calculated column from your data set. Uses EE_GetLastPopulatedCell to find the last cell of the data 
Sub EE_AddCalculatedColumn(rngColumn As range, strFormula As String, strNewHeading As String, Optional InChunksOf As Long)
    Dim rng As range
 
    With rngColumn
        Set rng = range(.Cells(2), .Cells(EE_GetLastPopulatedCell(rngColumn.Parent).Row))
    End With
    If Left(strFormula, 1) <> "=" Then strFormula = "=" & strFormula
 
    rngColumn.Cells(1, 1).value = strNewHeading
 
    If InChunksOf = 0 Then
        rngColumn.Cells(2, 1).value = strFormula
        With rng
            .Formula = rngColumn.Cells(2, 1).Formula
            .value = .value
        End With
    Else
        ' if chunks = 10, with 15 rows, do 1st 10, then next 5
        Dim i As Long
        Dim rngSplit As range
        For i = 1 To Application.RoundDown(rng.Rows.Count / InChunksOf, 0) + 1
            rngColumn.Cells(2, 1).value = strFormula
            Set rngSplit = range(rng.Cells((i - 1) * InChunksOf + 1, 1), rng.Cells(Application.WorksheetFunction.Min(i * InChunksOf, rng.Rows.Count), 1))
'            rngSplit.Select
            With rngSplit
                .FormulaR1C1 = rngColumn.Cells(2, 1).FormulaR1C1
                .value = .value
            End With
        Next
        rngColumn.Cells(2, 1).value = rngColumn.Cells(2, 1).value
    End If
 
End Sub
»