Adding sum formula in the merged cell.

Screenshot 2024-10-02 170207.png
Dear All,
If you have a VBA or other method which can detect the merged cells in the column j as per the image attached and insert the sum formula to those merged cells. The user will run the code for individual sheet. The code needs to detect the active sheet.

I tried to code but there are some syntax error.

Here is the code:

Sub MergedCells()
    Dim cell As Range
    Dim mergedRange As Range
    Dim formulaText As String
    Dim lastRowInJ As Long
    Dim firstRow As Long, lastRow As Long

    ' Find the last row in column J, starting from row 3
    lastRowInJ = ActiveSheet.Cells(ActiveSheet.Rows.Count, "J").End(xlUp).Row

    ' Loop through each cell in column J from row 3 downwards
    For Each cell In ActiveSheet.Range("J3:J" & lastRowInJ)
        ' Check if the cell is part of a merged range and is the first cell of that range
        If cell.MergeCells And cell.Address = cell.MergeArea.Cells(1, 1).Address Then
            ' Get the merged range
            Set mergedRange = cell.MergeArea

            ' Get the first and last row of the merged range
            firstRow = mergedRange.Row
            lastRow = firstRow + mergedRange.Rows.Count - 1
            
            ' Create the SUM formula for the corresponding range in column I
            formulaText = "=SUM(I" & firstRow & ":I" & lastRow & ")"

            ' Place the formula in the first cell of the merged range
            mergedRange.Cells(1, 1).Formula = formulaText
        End If
    Next cell
End Sub

Hi @arpansheth1356. You just need to correct this line:

lastRowInJ = ActiveSheet.Cells(ActiveSheet.Rows.Count, "J").End(xlUp).Row

to:

lastRowInJ = ActiveSheet.Cells(ActiveSheet.Rows.Count, "I").End(xlUp).Row

https://excel.solutions/2016/10/usin...ctively-excel/