Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks.

' This sub may be a start.
' It checks each cell in UsedRange.
' If C2:C4 are merged, it gets a hit
' on the merged cells 3 times.
' But it answers your example,
' giving =SUM($D$2:$D$4).

Sub Sub1()
Dim zCell As Range, iRowA&, iRowZ&, iCol%
For Each zCell In ActiveSheet.UsedRange
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowA = zCell.Row
iRowZ = iRowA + zCell.MergeArea.Rows.Count - 1
iCol = zCell.Column + 1
zCell.Formula = "=sum(" & _
Cells(iRowA, iCol).Address & _
":" & _
Cells(iRowZ, iCol).Address & _
")"
End If
Next zCell
End Sub ' D-C Dave

SteveDB1 wrote:
Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks.

Dave,
It appears to work. Thank you.
Next question. I noticed that it places the result in the merged area.
I would like to have the value placed where where I choose....
As previously stated, I'm looking at column C to determine if it's merged or
not (it will always have text in it.), and if so, to have values located in
column D to be summed over in another column/cell.

I want the sum valued results in column e. So, how would I place it there?

The UsedRange element, can I present that to look in a specific
column/range? if so, how would I write that statement? I ask that because my
worksheet will have 10 to 12 columns, and I only want it looking at the
merged rows of one specific column.

Again, thank you for your assistance.





"Dave D-C" wrote:

' This sub may be a start.
' It checks each cell in UsedRange.
' If C2:C4 are merged, it gets a hit
' on the merged cells 3 times.
' But it answers your example,
' giving =SUM($D$2:$D$4).

Sub Sub1()
Dim zCell As Range, iRowA&, iRowZ&, iCol%
For Each zCell In ActiveSheet.UsedRange
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowA = zCell.Row
iRowZ = iRowA + zCell.MergeArea.Rows.Count - 1
iCol = zCell.Column + 1
zCell.Formula = "=sum(" & _
Cells(iRowA, iCol).Address & _
":" & _
Cells(iRowZ, iCol).Address & _
")"
End If
Next zCell
End Sub ' D-C Dave

SteveDB1 wrote:
Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks.