+ Reply to Thread
Results 1 to 4 of 4

Adding sum formula in the merged cell.

Hybrid View

arpansheth1356 Adding sum formula in the... 10-03-2024, 12:58 AM
MikeVol Re: Adding sum formula in the... 10-03-2024, 03:32 AM
arpansheth1356 Re: Adding sum formula in the... 10-03-2024, 07:08 AM
Pepe Le Mokko Re: Adding sum formula in the... 10-03-2024, 04:23 AM
  1. #1
    Registered User
    Join Date
    10-03-2024
    Location
    ahmedabad
    MS-Off Ver
    2016
    Posts
    4

    Lightbulb 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
    Last edited by arpansheth1356; 10-03-2024 at 07:09 AM.

  2. #2
    Valued Forum Contributor MikeVol's Avatar
    Join Date
    12-30-2020
    Location
    Odessa / Ukraine
    MS-Off Ver
    MSO Prof Plus 2021 x64 (En)
    Posts
    468

    Re: Adding sum formula in the merged cell.

    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
    NOTE: As the original poster/owner, only you can mark your thread as SOLVED (Thread Tools above Post #1).
    You can say "Thanks" in your thread to everyone who offered to help you.
    You can also reward them by clicking * "Add Reputation" under their username on the left.
    With Regards, MikeVol.

  3. #3
    Registered User
    Join Date
    10-03-2024
    Location
    ahmedabad
    MS-Off Ver
    2016
    Posts
    4

    Re: Adding sum formula in the merged cell.

    Thank you it worked.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Adding sum formula in the merged cell.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If formula with merged cell
    By annu01 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-13-2022, 03:44 AM
  2. [SOLVED] Merged cell Formula
    By RJ1969 in forum Excel General
    Replies: 9
    Last Post: 09-15-2020, 07:39 PM
  3. Merged cell error on copy/paste, but no merged cells
    By OmniBlue in forum Excel General
    Replies: 3
    Last Post: 07-15-2019, 02:15 PM
  4. Replies: 1
    Last Post: 09-27-2018, 11:22 AM
  5. Limit merged cell data; Place excess into next merged cell down
    By coreytroy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2013, 10:35 PM
  6. [SOLVED] VB:ClearContents Error on Merged Cell - But I dont have Any Merged Cell
    By cychua in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-24-2013, 06:33 AM
  7. [SOLVED] how do i link merged cells to a merged cell in another worksheet.
    By ibbm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2006, 06:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1